topic Re: Previous() Statement Help in QlikView

Previous() Statement Help

Anonymous — Tue, 28 Feb 2017 21:23:26 GMT

I'm attempting to do a peek "running total" based of the previous date in my table of data. Currently, I have this listed as the statement in the script as:

IF(DATE = Previous(DATE) , (RangeSum(SUM(SOLDPERDAY), Peek('RunningTotal')))) as RunningTotal

The issue I am having, is that it is completely ignoring the if portion and just summing all (see below):

Date	Time	SOLDPERDAY	RunningTotal
4/19/2013 0:00	99%	37	21649
4/19/2013 0:00	100%	195	21844
4/22/2013 0:00	1%	2	21846
4/22/2013 0:00	2%	4	21850
4/22/2013 0:00	3%	-2	21848
4/22/2013 0:00	5%	4	21852
4/22/2013 0:00	6%	6	21858
4/22/2013 0:00	8%	-2	21856

What i want to see is this:

Date	Time	SOLDPERDAY	RunningTotal
4/19/2013 0:00	99%	37	21649
4/19/2013 0:00	100%	195	21844
4/22/2013 0:00	1%	2	2
4/22/2013 0:00	2%	4	6
4/22/2013 0:00	3%	-2	4
4/22/2013 0:00	5%	4	8
4/22/2013 0:00	6%	6	14
4/22/2013 0:00	8%	-2	12

Any help would be greatly appreciated

Re: Previous() Statement Help

aarkay29 — Tue, 28 Feb 2017 22:07:45 GMT

may be this

IF(Date = Previous(Date) , (RangeSum(SOLDPERDAY, Peek('RunningTotal'))),SOLDPERDAY) as RunningTotal

Re: Previous() Statement Help

swuehl — Tue, 28 Feb 2017 22:21:20 GMT

How does your complete LOAD statement look like?

Are you using a GROUP BY clause ( I noticed a Sum() aggregation function)?

You may also consider using Peek() instead of Previous()

IF(DATE = Peek(DATE) , (RangeSum(SUM(SOLDPERDAY), Peek('RunningTotal'))),SUM(SOLDPERDAY) ) as RunningTotal

Re: Previous() Statement Help

Anonymous — Wed, 01 Mar 2017 13:59:42 GMT

Yes, there is a group by:

LOAD

IF(DATE= Previous(DATE), (RangeSum(SUM(SOLDPERDAY), Peek('RunningTotal'))))) as RunningTotal,

NAME,

DATE,

time,

SUM(SOLDPERDAY) AS perDayCOUNT

RESIDENT RECONFIGURED_TICKETING3

Group By time, DATE, NAME;

Re: Previous() Statement Help

swuehl — Wed, 01 Mar 2017 14:08:14 GMT

Not sure if the previous() will work correctly / as expected here.

Maybe separate the group by load from the running total, or try with a sorted input table and Peek() [though I am not sure if this will work correctly, either]:

LOAD

IF(DATE= Peek(DATE), (RangeSum(SUM(SOLDPERDAY), Peek('RunningTotal')))), SUM(SOLDPERDAY) as RunningTotal,

NAME,

DATE,

time,

SUM(SOLDPERDAY) AS perDayCOUNT

RESIDENT RECONFIGURED_TICKETING3

Group By time, DATE, NAME

Order BY NAME, DATE, time;

Re: Previous() Statement Help

aarkay29 — Wed, 01 Mar 2017 14:08:39 GMT

LOAD *,

IF(Date = Previous(Date) , (RangeSum(perDayCOUNT, Peek('RunningTotal'))),perDayCOUNT) as RunningTotal;

Load

NAME,

DATE,

time,

SUM(SOLDPERDAY) AS perDayCOUNT

RESIDENT

RECONFIGURED_TICKETING3

Group By

time, DATE, NAME;

Re: Previous() Statement Help

Anonymous — Wed, 01 Mar 2017 14:50:40 GMT

If i use peek, it says the memory allocated is exceeded, and i'm not sure how to break it in to two, as it continues to throw an error. any thoughts of how to break it in to two?

Re: Previous() Statement Help

swuehl — Wed, 01 Mar 2017 14:55:02 GMT

Something like

TMP:

LOAD

NAME,

DATE,

time,

SUM(SOLDPERDAY) AS perDayCOUNT

RESIDENT RECONFIGURED_TICKETING3

Group By time, DATE, NAME;

RESULT:

LOAD

IF(DATE= previous(DATE), RangeSum(perDayCount, Peek('RunningTotal') ), perDayCount) as RunningTotal,

NAME,

DATE,

time,

perDayCount

RESIDENT TMP

ORDER BY NAME, DATE, time; // double check if these DATE breaks are what you need

DROP TABLE TMP;

Re: Previous() Statement Help

Anonymous — Wed, 01 Mar 2017 15:08:34 GMT

Its still ignoring the previous date

Re: Previous() Statement Help

swuehl — Wed, 01 Mar 2017 15:25:14 GMT

Ok, to clarify your issue, please upload some sample data, best as INLINE table in a QVW script.