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

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

Hi,

 

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
tresesco
MVP
MVP

Could you share your sample app to check?
Sameer9585
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'))))
sunny_talwar

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.

klrameet
Contributor III
Contributor III
Author

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.

 

S.png

 

Appreciate your help.

sunny_talwar

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

klrameet
Contributor III
Contributor III
Author

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

sunny_talwar

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

gajapathy74
Creator II
Creator II

Hi,

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')
,num($1,'#,##0')
));

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.

klrameet
Contributor III
Contributor III
Author

attached is sample qvf