RangeSum and Aggregate Combination Failing?

Stedi — Thu, 11 May 2023 20:00:53 GMT

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:

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:

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:

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?

Re: RangeSum and Aggregate Combination Failing?

Kushal_Chawda — Thu, 11 May 2023 20:31:46 GMT

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

Re: RangeSum and Aggregate Combination Failing?

Stedi — Fri, 12 May 2023 10:16:05 GMT

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?

topic RangeSum and Aggregate Combination Failing? in QlikView

RangeSum and Aggregate Combination Failing?

Re: RangeSum and Aggregate Combination Failing?

Re: RangeSum and Aggregate Combination Failing?