Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
i have a data - pre pandemic and post pandemic. I have pre calculated weekday averages for pre pandemic period.
For eg Monday avg = 14, Tuesday avg = 13.....Friday =10 etc
Now, i want formula to pick up the value of latest date from data then pull the pre pandemic value for same weekday by checking condition if the weekday of the latest date is equal pre pandemic weekday avg
Lets say today is friday and value is 3, pre pandemic avg for friday is 10 as per pre calculated field. The final output should be 3 by 10 which is 30%
Attaching the sample data for reference
Please follow the below code in the Script Editor first:
SET DayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';
NoConcatenate
Temp:
Load * Inline [
Date,Weekday,PrepandemicValue,PostpandemicValue
, Monday,14,
, Thursday,13,
, Tuesday,13,
, Wednesday,16,
, Friday,10,
, Saturday,18,
, Sunday,19,
27/06/2022,,,6
28/06/2022,,,4
29/06/2022,,,5
30/06/2022,,,7
01/07/2022,,,3
];
NoConcatenate
Temp1:
Load Date(Max(Date,'DD/MM/YYYY')) as Maxday,
Weekday(Max(Date)) as MaxWeekday
Resident Temp;
Let VMaxday=Peek('Maxday',0,'Temp1');
Let VMaxWeekday=Peek('MaxWeekday',0,'Temp1');
NoConcatenate
Temp2:
Load Date,
PostpandemicValue,
'Postpandemic' as Flag
Resident Temp
where Date='$(VMaxday)';
Concatenate
Load Weekday,
PrepandemicValue,
'Prepandemic' as Flag
Resident Temp
Where Weekday='$(VMaxWeekday)';
Drop table Temp, Temp1;
Exit Script;
In the Front End Sum(PostpandemicValue)/Sum(PrepandemicValue)
Expected output screenshot is attached.
If this resolved your issue, please like and accept it as solution.