Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Good Day,
I am currently struggling to build a cumulative chart.
I have a hard-coded Daily Target value and would like the Qty Sold and Target to accumulate daily.
Also, based on the date selection, the value must not change. Eg: When I select 2018/04/05 the target must be 900 and NOT 300. The Qty must be 1 917 NOT 879. Please see table below for more clarity and reference.
Please note I would like to do this transformation in the Load script.
Thanks In advance.
Hi Skzwane,
Try this:
A:
LOAD * INLINE [
TDate, Qty Sold, Target
4/3/2018, 692, 300
4/4/2018, 346, 300
4/5/2018, 879, 300
4/6/2018, 280, 300
4/7/2018, 21, 300
4/9/2018, 760, 300
];
B:
LOad
TDate,
[Qty Sold],
Target,
if(TDate<>Previous(TDate),Rangesum([Qty Sold],peek([Cumm Qty])),[Qty Sold])as [Cumm Qty],
if(TDate<>Previous(TDate),Rangesum(Target,peek([Cumm Target])),Target)as [Cumm Target]
Resident A
Order by TDate;
Drop table A;
PFB
T:
LOAD * INLINE [
Date, Daily Target, Qty Sold
2018/12/01, 300, 100
2018/12/02, 300, 100
2018/12/03, 300, 100
2018/12/04, 300, 100
2018/12/05, 300, 100
2018/12/06, 300, 100
];
F:
load Date,[Daily Target],[Qty Sold],
RangeSum([Daily Target],Peek('Test')) as Test,
RangeSum([Qty Sold],Peek('Test1')) as Test1
Resident T;
Test and Test1 are cumulative sums.
Also you can do it in the front end using the expression
RangeSum(Above(Sum([Qty Sol]),0,rowno()))
Ag+