Skip to main content
Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
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

morab
Contributor III
Contributor III
Author

You Are Super! Smiley Happy

Working like magic HeartSmiley LOL