Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ******
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.
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.
Hi Ruben
The number still not adding up correctly
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.
Thank you Ruben