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

[klrameet]

Hi,

@sunny_talwar , @Michael_Tarallo , @tresesco 

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.

[tresesco]

Could you share your sample app to check?

[Sameer9585]

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]

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.

[sunny_talwar]

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

[klrameet]

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]

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]

attached is sample qvf