Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a requirement where I need to do a calculated dimension and use it as a dimension in qlikview bar chart. I use the below in expression tab and works fine. Not sure how to use it as a dimension,
if(rangesum( above( aggr(sum(Counts),Month),0,4) )<=1,'No count',if(rangesum( above( aggr(sum(Counts),Month),0,4) )>=61,'Normal count','Low count'))
My solution is to use this as a dimension and I calculate the count of employees under each category for a bar chart. I am using the above expression to calculate the rolling 3 months on employee level. Is it better to do it at a script level or chart level.
If you want the calculation to be dynamic, you cannot have it in the script. So, an Aggr() call is the best approach.
Try putting Aggr() outside the entire calculation:
aggr(
if(rangesum( above( sum(Counts),0,4) )<=1,'No count',
if(rangesum( above( sum(Counts),0,4) )>=61,'Normal count',
'Low count')),
Month)
Then you should be able to use it as dimension. You may also need to use a set expression to remove a selection in Month, e.g. Sum({<Month= >} Counts).
I tried your solution, but when I use the above aggr function as a dimension, it does the sum on Month and does not do a rolling 3 month calculation.