topic Re: Accumulated count in script in QlikView

Accumulated count in script

andrespa — Fri, 16 Feb 2018 11:58:41 GMT

Hi All, I need to do something it may be simple but so far I'm no able to get it. So basically what I want is illustrated in the "AccumulatedCount" column below:

Key	StartDate	EndDate	Count	AccumulatedCount
1	10/02/2018	12/02/2018	1	1
3	11/02/2018	13/02/2018	1	2
4	12/02/2018	14/02/2018	1	3
5	13/02/2018	15/02/2018	1	4
5	14/02/2018	16/02/2018	1	5

The closest shoot I got so far is use this expression:

if(StartDate< Previous(EndDate), Count+1, Previous(Count)) AS AccumulatedCount

But, of course that's not what I want cause I'm summing 1 to 1 all the time:

Key	StartDate	EndDate	Count	AccumulatedCount
1	10/02/2018	12/02/2018	1	1
3	11/02/2018	13/02/2018	1	2
4	12/02/2018	14/02/2018	1	2
5	13/02/2018	15/02/2018	1	2
5	14/02/2018	16/02/2018	1	2

So I need something like that expression but over the AccumulatedCount field but not sure how to do it. Hope it makes sense.

Cheers,

Andrés

Re: Accumulated count in script

sunny_talwar — Fri, 16 Feb 2018 12:01:43 GMT

Try this

RangeSum(Peek('AccumulatedCount'), 1) as AccumulatedCount

Re: Accumulated count in script

andrespa — Fri, 16 Feb 2018 12:23:33 GMT

It doesn't work. The problem really is that you need to add 1 every single time the conditions fulfills (i.e. a simple a++ in any programming language) but here in the load editor you can't just do that. So I'm not sure if this is doable or you need to do it in the chart instead (although the same problem might happen there).

Not sure how to solve it.

Re: Accumulated count in script

sunny_talwar — Fri, 16 Feb 2018 12:27:04 GMT

You can, try this

If(StartDate < Previous(EndDate), RangeSum(Peek('AccumulatedCount'), 1), Peek('AccumulatedCount')) as AccumulatedCount

Re: Accumulated count in script

andrespa — Fri, 16 Feb 2018 12:54:00 GMT

That's exactly what I tried before. But something weird is happening or I'm missing something cause even in a table chart using the below expression and simplifying it without taking into account the date fields:

RangeSum(Above(SessionCount,0))

Which should work to gave me and accumulated value, it's just displaying always 1.

The same thing happens in the script if I use peek.

Re: Accumulated count in script

sunny_talwar — Fri, 16 Feb 2018 12:56:16 GMT

Not really, you have not used RowNo()

RangeSum(Above(SessionCount, 0, RowNo()))

Re: Accumulated count in script

andrespa — Fri, 16 Feb 2018 13:00:25 GMT

Indeed, you're right but still not working:

Re: Accumulated count in script

sunny_talwar — Fri, 16 Feb 2018 13:01:17 GMT

You might have more than one dimensions in there.... Try this

RangeSum(Above(TOTAL SessionCount, 0, RowNo(TOTAL)))

Re: Accumulated count in script

andrespa — Fri, 16 Feb 2018 13:06:54 GMT

Perfect! Didn't know you need to do that when you have more than 1 dimension. Thanks a lot, you've been very helpful!

Cheers,

Andrés

Re: Accumulated count in script

sunny_talwar — Fri, 16 Feb 2018 13:18:16 GMT

But this will continue to accumulate regardless of change in any of your dimensions.... if that is what you want, then that is great... else you will have to play around with your expression to make it to work

Re: Accumulated count in script

andrespa — Fri, 16 Feb 2018 13:22:08 GMT

Although if I tried to applied the date logic to it or put it into the script I fail miserably:

if([Session Start]< Above([Session End]), RangeSum(Above(TOTAL SessionCount,0, RowNo(TOTAL))), Above(SessionCount)) // Chart Not working

if([Session Start]< Previous([Session End]), RangeSum(Peek(SessionCount,0, RowNo())), Peek(SessionCount)) AS RollingSessionCount // Script Not working

Re: Accumulated count in script

andrespa — Fri, 16 Feb 2018 13:23:24 GMT

Yes, indeed that's not what I really want, but it's a step forward. I'll try to figure it out playing around yest.

Re: Accumulated count in script

sunny_talwar — Fri, 16 Feb 2018 13:25:46 GMT

May be provide few rows of data where the condition is met and where the condition isn't met and then share the output you expect to see from it?

Re: Accumulated count in script

andrespa — Fri, 16 Feb 2018 13:50:13 GMT

Sure, this is an xlsx sample file and the image below explains what I want:

Hope it makes sense

Re: Accumulated count in script

sunny_talwar — Fri, 16 Feb 2018 14:16:45 GMT

Try this

Table:

LOAD StartDate,

EndDate,

Session

FROM

[..\..\Downloads\AccumulatedCount.xlsx]

(ooxml, embedded labels, table is Sheet1);

FinalTable:

LOAD *,

If(StartDate <= Previous(EndDate), RangeSum(Peek('Accu'), Session), Alt(Peek('Accu'), Session)) as Accu

Resident Table

Order By StartDate;

DROP Table Table;