Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
WE ARE LISTENING! New Navigation for Qlik Community, Sept. 26: TELL ME MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
polisetti
Creator
Creator

Creating Flags from Concatenated Objects

Hi Everyone, 

I am facing an issue with creating flags considering the concatenated data model. I have weekly sales data snapshots at an account level that get created and need to use these snapshots and display different metrics (growth of an account, New Sale, and more)

I have concatenated rolling 7 weeks (Q1'2022) snapshots and displayed the week over week analysis and current week Sales etc. However, I have a specific flag to calculate 'New Sale' - this is considered to be an account that is not present in last week's snapshot but available in this week's snapshot (first use case) ex: Account2. Also, an account which is <100 of %Acheive in last snapshots and made 100% in this snapshot is considered as 'New Sales' Ex: Account1.

I want to achieve this at the data model level after concatenating all 7 weeks of snapshots. Could you please someone guide me correct direction.

Account %Acheive Week_Snap Amount Flag
Account1 75 1/31/2022 100  
Account1 75 7/2/2022 100  
Account1 100 2/14/2022 100 New Sale
Account2 100 2/14/2022 200 New Sale

 

 

Thanks,

Labels (3)
1 Reply
chrismarlow
Specialist II
Specialist II

Hi,

Assuming you do want to do this in script you could start with something like this;

data:
load * inline [
Account,	%Acheive,	Week_Snap,	Amount
Account1,	75,	31/01/2022,	100	 
Account1,	75,	07/02/2022,	100	
Account1,	100,	14/02/2022,	100
Account2,	100,	14/02/2022,	200
];

left join (data)
load date(min(Week_Snap)) as min_date
Resident data;

data_final:
NoConcatenate
LOAD
	Account,
	%Acheive,
	Week_Snap,
	Amount,
	If(RowNo()=1,
		if(Week_Snap<>min_date,
			'New Sale',
			null()
			),
		if(Account<>Peek('Account'),
			if(Week_Snap<>min_date,
				'New Sale',
				null()
				),
			if(Week_Snap-Peek('Week_Snap')<>7 or ([%Acheive]>=100 and Peek('%Acheive')<100),
				'New Sale',
				null()
				)
			)
		) AS Flag
Resident data
Order BY Account, Week_Snap;

drop table data;

Cheers,

Chris.