topic Re: Aggregate Measure Expression by class() When Dealing with Rangesum() Across 2 Dimensions in a C in App Development

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

Sazabi — Wed, 23 Jan 2019 21:27:25 GMT

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/45217#M3041), 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

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

Anil_Babu_Samineni — Thu, 24 Jan 2019 06:23:27 GMT

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

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

sunny_talwar — Thu, 24 Jan 2019 13:10:26 GMT

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?

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

Sazabi — Thu, 24 Jan 2019 18:23:53 GMT

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.

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

sunny_talwar — Thu, 24 Jan 2019 18:35:23 GMT

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

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

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

Sazabi — Thu, 24 Jan 2019 18:43:53 GMT

Will do Sunny!

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

is it the field2 within the class()?

Thanks!

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

sunny_talwar — Thu, 24 Jan 2019 18:46:48 GMT

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

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

Sazabi — Fri, 25 Jan 2019 17:27:44 GMT

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

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

sunny_talwar — Fri, 25 Jan 2019 18:22:25 GMT

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

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

Sazabi — Tue, 12 Feb 2019 19:59:15 GMT

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.

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

sunny_talwar — Wed, 13 Feb 2019 12:08:45 GMT

Would you be able to share a qvf file to show what you did? Like a sample file