topic Re: Cumulative Daily Target in QlikView

Cumulative Daily Target

skzwane01 — Sat, 16 Nov 2024 04:59:54 GMT

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.

Re: Cumulative Daily Target

jyothish8807 — Tue, 11 Dec 2018 11:49:35 GMT

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;

Re: Cumulative Daily Target

shiveshsingh — Tue, 11 Dec 2018 11:56:30 GMT

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
];

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.

Re: Cumulative Daily Target

albert_guito — Tue, 11 Dec 2018 12:06:30 GMT

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

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

Ag+