Skip to main content
Announcements
Introducing Qlik Answers: A plug-and-play, Generative AI powered RAG solution. READ ALL ABOUT IT!
cancel
Showing results for 
Search instead for 
Did you mean: 
Stedi
Contributor
Contributor

RangeSum and Aggregate Combination Failing?

Hi all!

I have a simple table with 1 dimension and 1 measure. The dimension is calendar days and named entitlement_event_date. The measure is calculating revenue increase (positive values) or decrease (negative values) for that day. The expression used is:

sum(if((entitlement_event_type = 'Start' or entitlement_event_type = 'End')
and entitlement_event_date >= Yearstart(ReloadTime())
and customer_since < '2023-01-01', event_price))

The result looks like this:

Screenshot 2023-05-11 at 21.51.20.png

 

 

 

 

 

 

 

 

 

 

 

This is step nr. 1.

Now, in a second step, I'd like to see the accumulated value from the beginning up, so I do:

rangesum(above(
sum(if((entitlement_event_type = 'Start' or entitlement_event_type = 'End')
and entitlement_event_date >= Yearstart(ReloadTime())
and customer_since < '2023-01-01', event_price))
, 0, rowno()))

 Result:

Screenshot 2023-05-11 at 21.53.42.png

 

 

 

 

 

 

 

 

 

 

 

Up to this point, everything works very well as expected.

Now to the challenge. As a next step, I'd like to bring in a new dimension (not visible in the table but for the calculation) because I want to do the calculation not globally but for each customer individually in order to be able to then only look at those customers where the revenue is decreasing, so I changed my expression to:

sum(aggr(

if(

rangesum(above(
sum(if((entitlement_event_type = 'Start' or entitlement_event_type = 'End')
and entitlement_event_date >= Yearstart(ReloadTime())
and customer_since < '2023-01-01', event_price))
, 0, rowno())) < 0,

rangesum(above(
sum(if((entitlement_event_type = 'Start' or entitlement_event_type = 'End')
and entitlement_event_date >= Yearstart(ReloadTime())
and customer_since < '2023-01-01', event_price))
, 0, rowno()))

)

, customer_short))

However, the result is not what I like to see:

Screenshot 2023-05-11 at 21.56.24.png

 

 

 

 

 

 

 

 

 

 

 

It should actually look the same as nr. 2 but with the positive values being NULL (only the first 3 shown) because currently there's exactly one customer selected as a selection (no other selections). In the end, I want to see all the negative customers and only them summed up when I remove all selections.

What am I doing wrong?

Labels (3)
2 Replies
Kushal_Chawda

@Stedi  not sure how would you like your accumulation with second dimension as I don't have sample to work on but you could try below

aggr(rangesum(above(
sum(if((entitlement_event_type = 'Start' or entitlement_event_type = 'End')
and entitlement_event_date >= Yearstart(ReloadTime())
and customer_since < '2023-01-01', event_price))
, 0, rowno())), customer_short)
Stedi
Contributor
Contributor
Author

Don't I need to sum the aggr so that I have one value representing the sum of all customers being negative instead of an array of values, one per customer?

I think that's exactly what I've tried in my third attempt but it doesn't work.

How can I provide a sample?