Skip to main content

I have written the calculation below. It get's me 90% of the way to where I need to be, but without the other 10% it's worthless. Hoping someone can help me with this one:

 

IF sum([header sum])>=1000000 then sum([header sum])/1000000

ELSEIF sum([header sum])>=1000 AND sum([header sum])<999999 then sum([header sum])/1000

ELSE IF sum([header sum])<1000 then sum([header sum])

END

END

 

The calc evaluates the 'header sum' value and converts it to trillions, billions, or millions as appropriate. I need to tack on the appropriate text (in red):

 

IF sum([header sum])>=1000000 then sum([header sum])/1000000 + 'trillion'

ELSEIF sum([header sum])>=1000 AND sum([header sum])<999999 then sum([header sum])/1000 + 'billion'

ELSE IF sum([header sum])<1000 then sum([header sum]) + 'million'

 

I suspect the answer may involve wrapping things in STR(), but I haven't been able to make it work.

 

Apologies for not posting a packaged workbook. It's proprietary and trying to scrub it was taking forever.

 

8 answers
  1. Oct 9, 2015, 1:27 PM

    Hi Doug

     

    I have replicated a basic version in superstore

    Hi Doug I have replicated a basic version in superstoreI'm hoping you can copy the methodology and apply it to your situation... This is now valid in Tableau...

    I'm hoping you can copy the methodology and apply it to your situation...

     

    This is now valid in Tableau...

     

    IF sum([Sales])>=1000000 then STR(sum([Sales])/1000000) + ' trillion'

    ELSEIF sum([Sales])>=1000 AND sum([Sales])<999999 then STR(sum([Sales])/1000) + ' billion'

    ELSEIF sum([Sales])<1000 then STR(sum([Sales])) + ' million' ELSE NULL END

     

    Cheers

    Mark

0/9000