Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Highlighted
Sazabi
New Contributor III

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

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

 

 

 

1 Solution

Accepted Solutions
Sazabi
New Contributor III

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

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.

 

image.png

 

 

10 Replies

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

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

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

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
New Contributor III

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

Sure Sunny!

 

With 1 Dimension :

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

image.png

 

With 2 Dimensions:

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

2. timestamp.autoCalendar.Date

image.png

 

The goal I am trying to get to (example): 
image.png

 

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

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
New Contributor III

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

Will do Sunny!

 

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

or

is it the field2 within the class()?

 

Thanks!

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

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

Sazabi
New Contributor III

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

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

 

1. 

 

image.png

 

2. image.png

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

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

Sazabi
New Contributor III

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

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.

 

image.png