Cumulative Daily Target

[skzwane01]

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.

[jyothish8807]

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;

[shiveshsingh]

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.

[albert_guito]

Also you can do it in the front end using the expression

RangeSum(Above(Sum([Qty Sol]),0,rowno()))

Ag+