topic Re: Cumulative in Qlikview Expression in QlikView

Cumulative in Qlikview Expression

morab — Sat, 16 Nov 2024 03:53:19 GMT

Hi All,

Good Day from Bangladesh!!!

Here, following is the data table from which we want to derive few outputs :

Data Table:

Date	Outlet Code	Product	Sales
1-Apr-19	1413601	DP	10
1-Apr-19	1413601	DDP	20
2-Apr-19	1413602	DP	5
2-Apr-19	1413603	DP	15
4-Apr-19	1413601	DP	10
4-Apr-19	1413603	DP	5
3-Apr-19	1413604	DDP	35
4-Apr-19	1413604	DP	15
4-Apr-19	1413605	DP	5

Expected Result:

1. New Placement: Number of outlet who have not purchase specific product during April 2019.

New Placement
Product	1-Apr-19	2-Apr-19	3-Apr-19	4-Apr-19
DP	1	2	0	1
DDP	1	0	1	0

2. Cumulative New Placement: By date - cumulative sum of new placement

Cumulative New Placement
Product	1-Apr-19	2-Apr-19	3-Apr-19	4-Apr-19
DP	1	3	3	4
DDP	1	1	2	2

3. Repeat Purchase : Number of outlet who have purchased specific Product 2 or more times

Repeat Purchase
Product	1-Apr-19	2-Apr-19	3-Apr-19	4-Apr-19
DP	0	0	0	2
DDP	0	0	0	0

Could you please help me?

Thanks in advance

Re: Cumulative in Qlikview Expression

sunny_talwar — Thu, 11 Apr 2019 11:05:16 GMT

Something like this

I created a flag in the script like this

Table:
LOAD Date(Date#(Date, 'D-MMM-YYYY')) as Date,
	 [Outlet Code],
	 Product,
	 Sales;
LOAD * INLINE [
    Date, Outlet Code, Product, Sales
    1-Apr-19, 1413601, DP, 10
    1-Apr-19, 1413601, DDP, 20
    2-Apr-19, 1413602, DP, 5
    2-Apr-19, 1413603, DP, 15
    4-Apr-19, 1413601, DP, 10
    4-Apr-19, 1413603, DP, 5
    3-Apr-19, 1413604, DDP, 35
    4-Apr-19, 1413604, DP, 15
    4-Apr-19, 1413605, DP, 5
];

FinalTable:
LOAD *,
	 If(Product = Previous(Product), If([Outlet Code] = Previous([Outlet Code]), 1, 0), 0) as RepeatFlag
Resident Table
Order By Product, [Outlet Code], Date;

DROP Table Table;

and then it was all about using the RepeatFlag in the set analysis

Re: Cumulative in Qlikview Expression

morab — Thu, 11 Apr 2019 12:12:53 GMT

You Are Super!

Working like magic