Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Join “Do More With Qlik” with Mike Tarallo to get a first-hand look at the New Authoring Experience, June 29th: REGISTER HERE
Showing results for 
Search instead for 
Did you mean: 
Contributor III
Contributor III

What is wrong with this expression, i am not able to make it work .. Help Appreciated !!



On Qlik Sense, I am trying to write an expression for one of the Measure Amount, to be displayed as a Bar Graph. Things I am trying to achieve here are:

1. Display Amount label as Billion, Million or K, based on underline amounts (currently if selection drops the total below Billion, it just calibrates Y-Axis to 0 Billion, not calibrating to Millions or Ks.

2. Regardless of the selection, if user makes any, always select/display bars for Report Comm X. So if user selects any other report, there should be two bars, one for Comm X and another for what user has selected

Expression I have managed to write based on my limited understanding of set analysis and qlik coding is as below, which clearly is not working.

Problems Faced:

1.Comm X is not getting selected as default Report, regardless of selection, it only displays report user has selected, if any

2. Amount is not getting displayed even in Billions, let alone be Million or K, it just coming as Number.





if(getselectedcount([REPORT])=0,Money(Sum(AMOUNT)/1000000000,'$ #,##0B'),
if(Sum(AMOUNT)>1000000000,Money(Sum({$<[REPORT]+={'Comm X'}>}[AMOUNT]/1000000000),'$ #,##0B'),
if(Sum(AMOUNT)>1000000,Money(Sum({$<[REPORT]+={'Comm X'}>}[AMOUNT]/1000000),'$ #,##0M'),Money(Sum({$<[REPORT]+={'Comm X'}>}[AMOUNT]),'$ #,##0'))))






Expression that works partially is as:





if(getselectedcount([REPORT])=0,Money(Sum(AMOUNT)/1000000000,'$ #,##0B'),Money(Sum({$<[REPORT]+={'Comm X'}>}[AMOUNT]/1000000000),'$ #,##0B'))






So far, this only displays amount in Billions on Y-Axis, doesn't do anything else that i am trying to achieve.


Please help.

Labels (4)
12 Replies

Could you share your sample app to check?
Creator II
Creator II

try this one?

if(GetSelectedCount([REPORT])=0,Money(Sum(AMOUNT)/1000000000,'$ #,##0B'),
if(Sum(AMOUNT)>1000000000,Money(Sum({$<[REPORT]+={'Comm X'}>}[AMOUNT])/1000000000,'$ #,##0B'),
if(Sum(AMOUNT)>1000000,Money(Sum({$<[REPORT]+={'Comm X'}>}[AMOUNT])/1000000,'$ #,##0M'),Money(Sum({$<[REPORT]+={'Comm X'}>}[AMOUNT]),'$ #,##0'))))

For 1st part of the problem, try this expression



=If(GetSelectedCount([REPORT]) = 0,
  Money(Sum(AMOUNT)/1000000000,'$ #,##0B'),
If(Sum({$<[REPORT] += {'Comm X'}>}AMOUNT) > 1000000000,
  Money(Sum({$<[REPORT] += {'Comm X'}>}[AMOUNT]/1000000000),'$ #,##0B'),
If(Sum({$<[REPORT] += {'Comm X'}>}AMOUNT) > 1000000,
  Money(Sum({$<[REPORT] += {'Comm X'}>}[AMOUNT]/1000000),'$ #,##0M'),
  Money(Sum({$<[REPORT] += {'Comm X'}>}[AMOUNT]),'$ #,##0'))))



Basically you need to add your set analysis to the Sum(AMOUNT) > check also.

Contributor III
Contributor III

Hi @sunny_talwar 


Thanks for the suggestion. This solves the problem (#1) partially.  Notice the images below, when there is no report selected, the Y-Axis should display scale in Billions, but it doesn't whereas when hovered upon, it shows amount with B.

On the second, when filtered further, the Y-Axis adjusts to show amount in Millions, but when hovered upon, it shows 0B, which is not correct.




Appreciate your help.


Is this the second issue or is this an issue where the numbers don't look right?

Contributor III
Contributor III

this is still first issue, where the numbers don't look right .. 😐


I guess it would be easy to work out the reason if you are able to share a sample. It is difficult to know all the moving parts without looking

Creator II
Creator II


Have the following in your load (edit) script below default variables:
//making Num format with Auto in expression
SET vScaleNumber=if($1>1000000, num($1/1000000,'#,##0.000M')
,if($1>1000, num($1/1000,'#,##0.000K')

the following expression should work without checking the value to show as B / M / K:
$(vScaleNumber(Sum({$<[REPORT] += {'Comm X'}>}[AMOUNT])))

hope this works for your portion of requirement.

Contributor III
Contributor III

attached is sample qvf