Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
morab
Contributor III
Contributor III

Cumulative in Qlikview Expression

Hi All,

Good Day from Bangladesh!!!

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

 

Data Table:

DateOutlet CodeProductSales
1-Apr-191413601DP10
1-Apr-191413601DDP20
2-Apr-191413602DP5
2-Apr-191413603DP15
4-Apr-191413601DP10
4-Apr-191413603DP5
3-Apr-191413604DDP35
4-Apr-191413604DP15
4-Apr-191413605DP5

 

Expected Result:

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

New Placement
Product1-Apr-192-Apr-193-Apr-194-Apr-19
DP1201
DDP1010

 

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

Cumulative New Placement
Product1-Apr-192-Apr-193-Apr-194-Apr-19
DP1334
DDP1122

 

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

Repeat Purchase
Product1-Apr-192-Apr-193-Apr-194-Apr-19
DP0002
DDP0000

 

Could you please help me?

 

Thanks in advance Smiley Happy

1 Solution

Accepted Solutions
sunny_talwar

Something like this

image.png

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

View solution in original post

2 Replies
sunny_talwar

Something like this

image.png

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

View solution in original post

morab
Contributor III
Contributor III
Author

You Are Super! Smiley Happy

Working like magic HeartSmiley LOL