How can I make a total YTD pivot table?

[rsapaull]

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	Item No
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	Item No
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?

[sunny_talwar]

Did you sort your data to be like this

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

[rsapaull]

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

[sunny_talwar]

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?

[rsapaull]

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.

[sunny_talwar]

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

[rsapaull]

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;

[sunny_talwar · Accepted solution]

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()))

[rsapaull]

Brilliant, that worked, thank you!