Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Problem with NUM Function and Rounding

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'))))

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

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

View solution in original post

4 Replies
Anonymous
Not applicable
Author

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'))))

Not applicable
Author

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

Anonymous
Not applicable
Author

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

Not applicable
Author

Got it!  Thank you!