Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
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.