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: 
NikosSpanos
Contributor III
Contributor III

Conditional Number Formatting

Why the below expression does not work in the expression tab of a KPI chart of Qlik Sense Service? Basically, it returns the value non-formatted without errors.

=IF($(Amount_Summary_StartDate)>=1000000000, Money($(Amount_Summary_StartDate)/1000000000, '#.##0,###B ', ',', '.'),

IF($(Amount_Summary_StartDate)>=1000000, Money($(Amount_Summary_StartDate)/1000000, '#.##0,###M ', ',', '.'),

IF($(Amount_Summary_StartDate)>=1000, Money($(Amount_Summary_StartDate)/1000, '#.##0,###K ', ',', '.'),

Money($(Amount_Summary_StartDate), '#.##0,###', ',', '.'))))

where $(Amount_Summary_StartDate) is a variable: sum({$<[Start_date]={'30.09.2020'}>}[Amount])

 

The result is: 13.32G while is should be: 13.321B

 

Relevant links: Link 1 , Link 2 , Link 3

Labels (3)
1 Solution

Accepted Solutions
Or
MVP
MVP

In theory, this should happen automatically... I don't think applying a specific formula would matter here. You could always use a numeric formula such as the ones you suggested to make sure it's numeric, but that would have always been the case to begin with.

 

Just to make sure - did you confirm that the variable formula returns the correct value in the first place (that is, 13321000000 when not formatted)? Did you confirm that you aren't already formatting the value within the variable (or within any field/formula it references) in a manner that might interfere with further formatting?

View solution in original post

8 Replies
Or
MVP
MVP

I've attempted to put the relevant part of your formula into a KPI object and I am getting 13,321B:

=Money(13321000000/1000000000, '#.##0,###B ', ',', '.')

Capture3.PNG

NikosSpanos
Contributor III
Contributor III
Author

The single formatted is also "Ok" in my version. However, I want the conditional formatted version because the number may not always be in Billions but in millions or thousands. Try to put an If else statement there and check the output.

Or
MVP
MVP

Same result for the full statement (replacing your formula since I don't have access to the data)

=IF(13321000000>=1000000000, Money(13321000000/1000000000, '#.##0,###B ', ',', '.'),

IF(13321000000=1000000, Money(13321000000/1000000, '#.##0,###M ', ',', '.'),

IF(13321000000=1000, Money(13321000000/1000, '#.##0,###K ', ',', '.'),

Money(13321000000, '#.##0,###', ',', '.'))))

 

NikosSpanos
Contributor III
Contributor III
Author

This is strange actually. Also in my version the code works if I replace the variable with 13321000000, which means that the value returned from variable has a problem and not the if conditioning.

Or
MVP
MVP

That's what it sounds like... a good debug option would be to hard-code the value into a variable and see if that works. If it does, the variable formula is the problem. If not, this might be a bug with using variables in formatting?

NikosSpanos
Contributor III
Contributor III
Author

I have hard-coded the number into a variable and indeed it shows the number correctly with the applied format. That drives us to the conclusion that the variable formula has the problem and not the number formatting conditions.

Is there any way to re-cast the type value of the formula? For example force the formula's output to be Floor, Integer, or something closer to 13321000000 which seems to be an Integer?

Or
MVP
MVP

In theory, this should happen automatically... I don't think applying a specific formula would matter here. You could always use a numeric formula such as the ones you suggested to make sure it's numeric, but that would have always been the case to begin with.

 

Just to make sure - did you confirm that the variable formula returns the correct value in the first place (that is, 13321000000 when not formatted)? Did you confirm that you aren't already formatting the value within the variable (or within any field/formula it references) in a manner that might interfere with further formatting?

NikosSpanos
Contributor III
Contributor III
Author

Yeah you are right, it must have gone through a pre-formatting step in the data load editor because If I return the formula's sum output I get back this number -3.69G which seems to be a formatted number.

Only if I change the measure number formatting, from the Data tab on the right, from Auto to Number I can take the whole number which is -3.691.333.999.19.  So I have to contact the person that loaded the data in order to spot the format pattern.