Creating Flags from Concatenated Objects

[polisetti]

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,

[chrismarlow]

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.