topic Re: Running total at max date in QlikView

Running total at max date

Thu, 24 Jul 2014 21:30:49 GMT

Hi,

I'm using the following expression to accumulate figures in a column:

sum(aggr(
RangeSum(Above(
sum(
aggr(
sum({<ID = {'34'}>} Total <[Visit IP Country ISO],[Visit Date]> Signups)
,[Visit IP Country ISO], [Visit Date])
)
, 0, RowNo()))
,[Visit IP Country ISO], [Visit Date]))

This works well, but the expression total is the sum of the accumulated values, whereas I need the expression total to be the running total at the max date. Can anyone think of a way to add this logic to the expression?

Here's a screenshot illustrating the output I'm after. 235, the running total at max date should be the expression total instead of 1001:

I've attached an example below. Apologies in advance, this is a repost, but I'm in a pinch and would like to get some help on this soon.

Matt

Re: Running total at max date

Thu, 24 Jul 2014 21:39:18 GMT

Attached with answer.

Select Max of Rows for Totals

Thanks

Re: Running total at max date

Thu, 24 Jul 2014 21:40:22 GMT

Re: Running total at max date

Thu, 24 Jul 2014 21:42:20 GMT

Hi Ajay,

Max isn't an option for me, it needs to be done with set analysis.

Any ideas how the expression can be modified to do this?

Matt

Re: Running total at max date

Thu, 24 Jul 2014 21:44:40 GMT

May I ask why Max wont work for you. It gives the desired result, doesn't it.

Re: Running total at max date

Thu, 24 Jul 2014 21:50:34 GMT

I need to use the expression alone in a text box as well as the straight table and the max option won't work there. In the straight table I'll also be dividing it to create another expression (see column 'CR BL' above) and need the expression total based on max date for this.

Best,

Matt

Re: Running total at max date

Fri, 25 Jul 2014 14:32:39 GMT

I've wrapped the expression in this: max(aggr( exp , [Visit Date])) and it's working.