topic Re: Problem with NUM Function and Rounding in QlikView

Problem with NUM Function and Rounding

Tue, 13 May 2014 15:44:47 GMT

I have the following function which I would like to have behave in the following way:

If the Sum Function (of the set of current selections minus the years 2011-2014) returns a value larger than a billion dollars, the number should display in billions ($#,##B). If not, and the value is larger than a million dollars, the number should display in millions ($#,##M). If not, and the value is larger than a thousand dollars, the number should display in thousands ($#,##K). If not, display in individual dollars ($#,##).

As it stands now, the SUM function works, but the Num Function doesn't seem to be working, other than inserting commas.

=IF(SUM({$-<FiscalYear={2011,2012,2013,2014}>}Amount)/1000000000>1,NUM(SUM({$-<FiscalYear={2011,2012,2013,2014}>}Amount),'$#,###0B'),IF(SUM({$-<FiscalYear={2011,2012,2013,2014}>}Amount)/1000000>1,NUM(SUM({$-<FiscalYear={2011,2012,2013,2014}>}Amount),'$#,##0M'),IF(SUM({$-<FiscalYear={2011,2012,2013,2014}>}Amount)/1000>1,NUM(SUM({$-<FiscalYear={2011,2012,2013,2014}>}Amount),'$#,##0K'),NUM(SUM({$-<FiscalYear={2011,2012,2013,2014}>}Amount),'$#,##0'))))

Re: Problem with NUM Function and Rounding

Anonymous — Tue, 13 May 2014 15:52:07 GMT

It looks like you are not including the divisor in each of the sum functions.

=IF(SUM({$-<FiscalYear={2011,2012,2013,2014}>}Amount)/1000000000>1,NUM(SUM({$-<FiscalYear={2011,2012,2013,2014}>}Amount)/1000000000,'$#,###0B'),IF(SUM({$-<FiscalYear={2011,2012,2013,2014}>}Amount)/1000000>1,NUM(SUM({$-<FiscalYear={2011,2012,2013,2014}>}Amount),'$#,##0M')/1000000,IF(SUM({$-<FiscalYear={2011,2012,2013,2014}>}Amount)/1000>1,NUM(SUM({$-<FiscalYear={2011,2012,2013,2014}>}Amount)/1000,'$#,##0K'),NUM(SUM({$-<FiscalYear={2011,2012,2013,2014}>}Amount),'$#,##0'))))

Re: Problem with NUM Function and Rounding

Tue, 13 May 2014 15:55:51 GMT

Ahhh, I misunderstood how NUM worked then. How do you designate how many decimal places it should round to then?

Re: Problem with NUM Function and Rounding

Anonymous — Tue, 13 May 2014 15:57:32 GMT

Change the format. For instance '$#,##0.0B' for 1 decimal place etc.

Re: Problem with NUM Function and Rounding

Tue, 13 May 2014 16:05:15 GMT

Got it! Thank you!