topic Re: Rangesum not working in QlikView

Rangesum not working

Wed, 18 Dec 2013 02:49:12 GMT

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!

Re: Rangesum not working

jagan — Wed, 18 Dec 2013 03:04:03 GMT

Hi,

Try like this

Data:

LOAD

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

FROM DataSource;

Regards,

Jagan.

Re: Rangesum not working

Wed, 18 Dec 2013 03:25:43 GMT

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!

Re: Rangesum not working

jagan — Wed, 18 Dec 2013 03:32:21 GMT

Hi,

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

Regards,

Jagan.

Re: Re: Rangesum not working

Wed, 18 Dec 2013 04:02:21 GMT

Hi Jagan,

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

thanks

Marko

Re: Rangesum not working

jagan — Wed, 18 Dec 2013 05:16:53 GMT

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.

Re: Re: Re: Rangesum not working

IAMDV — Wed, 18 Dec 2013 08:02:08 GMT

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,

Re: Rangesum not working

preminqlik — Wed, 18 Dec 2013 08:13:43 GMT

hi ,

PFA

regards

Premhas.

Re: Re: Re: Re: Rangesum not working

Wed, 18 Dec 2013 13:29:04 GMT

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;

Re: Re: Re: Rangesum not working

IAMDV — Wed, 18 Dec 2013 13:42:03 GMT

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,

Re: Re: Re: Rangesum not working

Wed, 18 Dec 2013 13:51:39 GMT

Hi Deepak,

My main sort criteria are Date and Sales. Sales needs to be sorted in a descending order and for the very last Sales record my AccSum must equal total range sum of Sales for that date.

Re: Re: Re: Rangesum not working

IAMDV — Wed, 18 Dec 2013 14:23:09 GMT

Hi Marko,

"My main sort criteria are Date and Sales. Sales needs to be sorted in a descending order and for the very last Sales record my AccSum must equal total range sum of Sales for that date".

Please can you provide sample data of end result in excel for sub set of data. I'm not following completely...sorry!

Cheers,

Re: Re: Re: Rangesum not working

Wed, 18 Dec 2013 14:42:59 GMT

Hi Deepak,

I attached qvw in my previous post and created a straight table that will give you data sorted as necessary. If you select 20131109 and export to XLS, do sum of the Sale column and you will get -881.447

I hope this helps.

Re: Re: Re: Re: Rangesum not working

jagan — Thu, 19 Dec 2013 03:08:10 GMT

Hi Marko,

In straight table sort the data by Accumulated Sum and you will get the last row as -881.447 which is equal to the Sum of all Sales values. Please check attached file.

Regards,

Jagan.

Re: Re: Re: Re: Rangesum not working

Thu, 19 Dec 2013 13:16:25 GMT

Your answer is in the right direction. The only problem I have now is that my keys that have 0 for sales do not get accumulated.

I also moved the logic from the script to a chart with rangesum(above(sum(..)),0,rowno())) expression.

If i didn't have randonm blanks in my table this would definitely be the correct answer.

Thank you.

Re: Re: Re: Re: Rangesum not working

Thu, 19 Dec 2013 13:17:29 GMT

Hi Jagan,

That won't work. I cannot sort by AccSum column as the requirement is to sort primarily by sales figures.

Thanks,