Rangesum not working

[#1]

Hello!

I'm having an issue with using rangesum in the script. Attached is an XLS extract of sample data and in column D I specified desired outcome.

When I use a single sales day my formula works and calculated rangesum works without a problem. However, when I add more days to my dataset, that is when rangesum gets out of whack.

I use:

if(Date= peek('Date', -1), rangesum(Sales, peek('RangeSum'))) as RangeSum

Any idea what I am doing wrong here? Data needs to be arranged from highest sales to lowest, by day.

Thanks!

[jagan]

Hi,

Try like this

Data:

LOAD

  *,

  If(Previous(Date) <> Date, Sales, Sales + Peek('AccumulatedSum')) AS  AccumulatedSum

FROM DataSource;

Regards,

Jagan.

[#3]

Hi,

Thanks for your answer.

Problem with this approach is that the very last record from each day is not added to the accumulated field.

Thanks!

[jagan]

Hi,

This works for any scenario, can you post some sample data where you are facing the issue.

Regards,

Jagan.

[#5]

Hi Jagan,

please see attached. Filter by a day and notice how cumulative sum breaks few records down the list.

thanks

Marko

[jagan]

Hi,

Can you attach the excel file which you used, did the records is sorted by date?  The dates should be sorted in ascending order then only this works.

Regards,

Jagan.

[IAMDV]

Hi Marko,

Jagan is right! You need to sort the table else you won't get the right result. This is because we are performing row by row comparison on Dates. Please see the below script...

Test:

LOAD Sales,

    Department,

    Date,

    RangeSum

FROM

(biff, embedded labels, table is Sheet1$);

Final:

LOAD *,

    If(Previous(Date) <> Date, Sales, Sales + Peek('AccumulatedSum')) AS  AccumulatedSum

    Resident Test

    Order by Date, Department ASC;

Drop Table Test;

Cheers,

DV

[preminqlik]

hi ,

PFA

regards

Premhas.

[#9]

Hi Deepak,

This almot worked for me.. Slight change on order by. One outstanding issue. When i create a straight table, select a date, and sort by Sales, I would expect the very last record from Acc..Sum colum to equal, e.g. for 2011 -1219 or for 2013 -1292. Last record from AccSum field for 2011 and 2013 are -850 and -969 respectively. Any way to sort that correctly as well?

Thanks!

LOAD Date,

     Key,
     Sale
FROM
tests.xls
(biff, embedded labels, table is [Sheet1$]);

Final:
LOAD *,
    If(Date<>Previous(Date), Sale, Sale + Peek('AccumulatedSum')) AS  AccumulatedSum
    Resident 1
    Order by Date, Sale desc;

[IAMDV]

Marko - Please can you post the sample application? Where do you want to sort AccumulatedSum?

PS - Ignore this message... I can see the attachement now.

Thanks,

DV