Simple table accumulation issue

[AmirKilkr]

Hello members,

I have an issue, I cant create the accumulation in the table below as needed in the following picture:

This is the script:

SalesAggFinal:

LOAD
YearMonth,
"Group",
Quantity,
TotalPriceBeforVat,
TotalPriceBeforVat/Quantity as unitprice
FROM [lib://DB/SalesAggFinal.qvd]
(qvd);

SalesAggAcc:

LOAD "Group",
YearMonth,
TotalPriceBeforVat, RangeSum(peek('Cumulative') , TotalPriceBeforVat) AS TotalPriceBeforVatCumulative

Resident SalesAggFinal;

exit script

Table after accumulation:

****Table before the accumulation ******

[rubenmarin]

Hi, try with:

SalesAggAcc:
LOAD "Group",
YearMonth,
TotalPriceBeforVat, 
If(Group=Peek(Group)
  ,RangeSum(peek('TotalPriceBeforVatCumulative'), TotalPriceBeforVat)
  ,TotalPriceBeforVat
) AS TotalPriceBeforVatCumulative
Resident SalesAggFinal
Order By Group, YearMonth;

I added a check to not accumualte different groups, if you want to accumulate differnt droups you can remove the order by and the If.

[AmirKilkr]

Hi Ruben

The number still not adding up correctly 

[rubenmarin · Accepted solution]

Hi, I see that YearMonth is leaft aligned, meaning the it's being loaded as a text, not a date, and it would be sorted alphabetically, not by date, so Feb comes first, then Jan and lastly Mar.

Check where are you creating this field and use one that is really a date, you can also crete one from this field using something like Date(Date#(YearMonth,'YYYY-MMM'),'YYYY-MMM') as YearMonthAsDate.

[AmirKilkr]

Thank you Ruben