Aggregate Measure Expression by class() When Dealing with Rangesum() Across 2 Dimensions in a Chart

[Sazabi]

Hello Qlik Friends!!

I am looking to do a date overlay of cumulative  totals. (one line of cumulative  totals per day on a line chart).

My 1st dimension: a calculated dimension using the class() function.

My 2nd dimension:  a date timestamp field.

My measure: rangesum(above(Count(field),0,rowno()))

Without the 2nd dimension, the linechart is fine as a summary, but once that 2nd dimension is put in, it does not calculate correctly. It seems the numbers aren't being accumulated by rangesum()..

Briefly scanning across the community, according to some other threads (like https://community.qlik.com/t5/Qlik-Sense-App-Development/RangeSum-with-2-Dimensions/m-p/15716#M1100 and https://community.qlik.com/t5/Qlik-Sense-App-Development/Rangesum-Above-with-Multiple-Dimensions/m-p...), I am supposed to aggr() my measure by the 2 dimensions.

Therefore my new measure should look like :

aggr(rangesum(above(Count(field),0,rowno())),class(),timestampfield)

However, they are turning my values null for me.

I wouldn't want to use TOTAL in this case either as I am looking for the cumulative sum by dates.

Are we able to aggr() by a class() function? 

Thank you very much

L

[Anil_Babu_Samineni]

As Qlik funda: We can't use calculated dimensions over Aggr(). So, I suggest you to directly use

aggr(rangesum(above(Count(field),0,rowno())),Dimension,timestampfield) // Class() always need one dimension, For that you can define dimension name

[sunny_talwar]

Would you be able to share an image of what you get when you use this

RangeSum(Above(Count(field), 0, RowNo()))

and point out what is not right and what would you want to get instead?

[Sazabi]

Sure Sunny!

With 1 Dimension :

class(aggr(sum(field),field2),2)

With 2 Dimensions:

1. class(aggr(sum(field),field2),2)

2. timestamp.autoCalendar.Date

The goal I am trying to get to (example): 

The first example with 1 dimension is perfect! 🙂

However, we can see in the second example that when the chart is broken out by date, rangesum appears to not be doing its job...even when filtered to a single date.

Ideally I want to see the first example broken out per day.

[sunny_talwar]

Not sure if this will work or not, but can you try this

Aggr(
     RangeSum(Above(Count(field), 0, RowNo()))
, timestamp.autoCalendar.Date, field2)

[Sazabi]

Will do Sunny!

In this case, is field2 = class(aggr(sum(field),field2),2)

or

is it the field2 within the class()?

Thanks!

[sunny_talwar]

field2 within Class()... second option....

[Sazabi]

Hi Sunny, here is what it looks like with 1 and 2 days selected with your recommended expression

1. 

2. 

[sunny_talwar]

Not what I expected... would you be able to share a sample to play around with this?

[Sazabi · Accepted solution]

Hi Sunny,

Just coming back to this so people of the future can look back at this:

I was able to resolve my issue by configuring the sorting order of the chart.

Under dimensions, I set #1 to be the class(aggr()) and #2 to be the timestamp.date

Under sorting order, I set #1 to timestamp.date, #2 to the class(aggr())

By doing this, a healthy overlay of % distribution was born.