topic Using RangeSum and Aggr in Script or Dimension in Qlik Automate

Using RangeSum and Aggr in Script or Dimension

Krish — Thu, 16 May 2024 08:48:24 GMT

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.

Re: Using RangeSum and Aggr in Script or Dimension

hic — Mon, 20 May 2024 15:44:56 GMT

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).

Re: Using RangeSum and Aggr in Script or Dimension

Krish — Mon, 20 May 2024 21:26:30 GMT

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.