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.

[sunny_talwar]

So, do you need the whole chart to show the same B, M? Because right now each bar decides if the Sum(Amount) is greater than a value and displays a value which is why you might be running into this issue. Also, do you want to may be check the absolute value of the Sum(Amount) instead of just Sum(Amount)? Please check to see if the attached make sense

[klrameet]

i am hoping to get it adjusted dynamically, so that if atleast one bar is over billions, it should scale up to display amount in billions on Y-axis, but as you narrow down search, and bring the amount ot millions or Ks, axis scale should re-calibrate itself.

[sunny_talwar]

I don't think I know a way to do this, but somebody else might.