How to calculate % value between 2 time period with same weekday condition ?

[pal25]

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

[sidhiq91]

@pal25 

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.