Qlik Community

Qlik Sense App Development

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

Announcements
Customers, Partners & Luminaries only: You're invited to a Data Analytics Roadmap session. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

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
Highlighted
Creator
Creator

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

 

 

View solution in original post

10 Replies
Highlighted

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

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?

Highlighted
Creator
Creator

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.

 

Highlighted

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

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

Will do Sunny!

 

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

or

is it the field2 within the class()?

 

Thanks!

Highlighted

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

Highlighted
Creator
Creator

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

 

1. 

 

image.png

 

2. image.png

Highlighted

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

Highlighted
Creator
Creator

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

 

 

View solution in original post