If today is Monday, need to check Previous Friday and Thursday Data

[Rev2091]

If today is a Monday, it should basically compare its sales to previous Thursday and Friday, in this case 29 09 2023 and 28 09 2023 and be picked up only when then the total sales amt is more than $5M on Monday and there is a decrease. else it should be ignored. likewise every other weekday will only get compared to the previous day with the same criteria where sales amt is more than $5m and there is a decrease. no weekends and public holidays are captured in this dataset. how do i do this in backend scripting to loop through dates to check. maybe with a flag? this script is running on a daily basis to capture the decrease and displays the last 7 days only with the lastest day being highlighted.

[ali_hijazi]

kindly accept my answer as a solution
One more thing, in case your transactions skipped a Thursday or Friday then you need to check if previous 3, or 4 are really a Friday and a Thursday
if(weekDay(_date) = 1 and weekday(peek(_date,-3) = 5)), peek(amount,-3)) as fri_amt
assuming sunday is the firs week day so Monday is 1, and Friday would be 5 (could be mistaken need to check it 🙂 )

I can't think of an alternative to this approach
preceeding loads are fast enough

[marcus_sommer]

Properly prepared within a master-calendar you won't need any if-loops to associate the wanted data. Beside using direct associations against the calendar like hinted above you could also derive various mapping-tables from the calendar which you queries in your other loads.

I suggest to go with an extended calendar in the mid- and long-term because it's much more easier and powerful as to develop multiple routines for date-queries again and again. But if the if-loops are working now then just remain by it.