topic Re: How can I make a total YTD pivot table? in QlikView

How can I make a total YTD pivot table?

rsapaull — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I am trying to make a table that plots the Item No, Total Sales and Development period together where the development period is a quarter of a year. Below is an example of sum of sales with Development period on the left and Item No at the top.

Dev Period	1	2	3	4
0	21	43	11	32
1	33	42	9	15
2	19	17	23
3	14	21
4	9	9
5	11	5
6	6
7	3

What I need is for it to create as an accumulation as below:

Dev Period	1	2	3	4
0	21	43	11	32
1	54	85	20	47
2	73	102	43
3	87	123
4	96	132
5	107	138
6	113
7	116

I've tried the following code for the sales as adapted from other forums, but it just gives me the total per development period without cumulatively summing it:

IF((Previous(DevelopmentQtr)=DevelopmentQtr), RangeSum(peek(Cum_Sales),Sales),Sales) as Cum_Sales

Can anyone help please?

Re: How can I make a total YTD pivot table?

sunny_talwar — Tue, 23 Oct 2018 13:02:13 GMT

Did you sort your data to be like this

Order By [Item No], [Dev Period];

Re: How can I make a total YTD pivot table?

rsapaull — Tue, 23 Oct 2018 13:56:15 GMT

How is sorting the data going to get it to add the value of the cell above?

Re: How can I make a total YTD pivot table?

sunny_talwar — Tue, 23 Oct 2018 14:02:46 GMT

1st I think you might be looking to check if(Previous([Item No]) = [Item No] instead of checking If(Previous(DevelopmentQtr) = DevelopmentQtr)... if this is true... let's imagine your data is sorted like this....

Dev Period Item No

0 1

0 2

0 3

1 1

1 2

1 3

....

If you check If(Previous(Item No) = ItemNo.... it is never going to be true because 1 <> 2 and 2 < 3 and 3 <> 1.... but if you sort first by Item No and then by Dev Period, you will get something like this

Dev Period Item No

0 1

1 1

0 2

1 2

0 3

1 3

....

Now you can check your condition properly.

Does this make sense?

Re: How can I make a total YTD pivot table?

rsapaull — Tue, 23 Oct 2018 15:13:39 GMT

Ordering makes no difference whatsoever, but I just checked my formula and swapped DevelopmentQtr with Item No and now found out that neither of them are right.

Re: How can I make a total YTD pivot table?

sunny_talwar — Tue, 23 Oct 2018 15:18:16 GMT

Would you be able to share few rows of raw data to look at what you have?

Re: How can I make a total YTD pivot table?

rsapaull — Tue, 23 Oct 2018 16:38:17 GMT

OK, well attached is some raw data, the code in my script is below and the pivot table I have has DevelopmentQtr and Item No as dimensions and the expression is sum(TRI_Sales).

TRITEMP:

LOAD

DevelopmentQtr,

Sales,

[Sale Date],

Year([Sale Date]) as [Sale Year],

[Item No],

IF((Previous([Item No])=[Item No]), RangeSum(Sales,peek('TRI_Sales')),Sales) as TRI_Sales

FROM

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

NoConcatenate

TRIANGLE:

LOAD*

Resident TRITEMP

Order By [Item No], DevelopmentQtr;

DROP Table TRITEMP;

Re: How can I make a total YTD pivot table?

sunny_talwar — Tue, 23 Oct 2018 16:50:15 GMT

Based on the kind of data you have, it might make sense to do it as an expression....

Try this

=RangeSum(Above(Sum(Sales), 0, RowNo()))

Re: How can I make a total YTD pivot table?

rsapaull — Tue, 23 Oct 2018 20:15:57 GMT

Brilliant, that worked, thank you!