Previous() Statement Help

[Anonymous]

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

[aarkay29]

may be this

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

[swuehl]

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

[Anonymous]

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;

[swuehl]

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;

[aarkay29]

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;

[Anonymous]

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?

[swuehl]

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;

[Anonymous]

Its still ignoring the previous date

[swuehl]

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