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.

[Rev2091]

i have tried using master calendar but did not succeed as weekends and public holidays should not be included. i have done up the basic scripting but stuck on the part where monday has to be compared with thursday and friday.

[ali_hijazi]

Hello
you can accomplish this using the above function and passing the step number
so if Monday then Friday is 3 steps before

if you are not able to resolve this issue kindly send me sample data and I will try to resolve it

[marcus_sommer]

The master-calendar is the right place to create/prepare all required information for such a scenario, for example with:

if(match(weekday(MyDate), 5, 6) or applymap('holidays', MyDate), 0, 1) as WorkingDayFlag

in a preceding load you could accumulate them, like:

if(Year = previous(Year),
   peek('WorkDaysYear') + WorkingDayFlag, peek('WorkDaysYear')) as WorkDaysYear

Now each date has the information if it's a working-day and it's running index-number in the year which you could query within a set analysis, like:

{< WorkDaysYear = {"$(=max(WorkDaysYear) - 1)"} >}

to get the associations to the previous working-day.

It may not be fulfill all your requirements especially in regard to the available data-model but it gives you a lot of ideas to prepare everything date-related within the calendar.

[Rev2091]

Hi, i am unable to upload the qvf hence please see below for sample data.

FACT_DEP:
Load
Date(Date#(DateKey,'YYYYMMDD'),'YYYYMMDD') as DateKey,
DEP_NUM_SMUN,
PreviousBalance_USD,
from [$(vQVDTransform_FactDEP)] (qvd);

And then i ranked the dates in dec order to extract last 7 days of data.

DEP:
Load *,
if (DateRank=1,Date(DateKey,'DD MMM YYYY')) as Day1,
if (DateRank=2,Date(DateKey,'DD MMM YYYY')) as Day2,
if (DateRank=3,Date(DateKey,'DD MMM YYYY')) as Day3,
if (DateRank=4,Date(DateKey,'DD MMM YYYY')) as Day4,
if (DateRank=5,Date(DateKey,'DD MMM YYYY')) as Day5,
if (DateRank=6,Date(DateKey,'DD MMM YYYY')) as Day6,
if (DateRank=7,Date(DateKey,'DD MMM YYYY')) as Day7;
Load *,
AutoNumber(DateKey) as DateRank
RESIDENT Deposits_Alert
WHERE AutoNumber(DateKey) >=1 and AutoNumber(DateKey) <=7
ORDER BY DateKey DESC;

And then i did a sum to get total balance for each date to check on the $5M criteria later on.

FACT_DEP:
LOAD * RESIDENT DEP;
LEFT JOIN
Load
DateKey,
DEP_NUM_SMUN,
sum(PreviousBalance_USD) as Amt_Bal_Total
RESIDENT DEP 
GROUP BY DateKey,DEP_NUM_SMUN;

After this part i am stuck on how to take it further.

[Rev2091]

but since my dataset itself doent contain any weekends and public holidays, i cant use master calendar right since it iterates through each date from min to max. its back to step 1 to filter out the dates i need etc.. However, let me test it out. thank you.

[marcus_sommer]

In most scenarios it doesn't matter that the facts didn't contain data for each date because each available date will have the right associations to year and month as well as all kinds of flags or accumulations or min/max ... what ever is needed.

By other dimensions-tables like articles or staff you will filter them against the facts to avoid of having thousands of old or unrelated dimension-values but by calendars is such an approach rather unusual.

[Rev2091]

without using master calendar, i have already ranked my dates in desc and got te last 7 biz days. however now i am stuck on the part where i need monday to be compared with thurs and fri sales to check if there is a decrease. 

[ali_hijazi]

Sort rows by date ascending
Then with a preceding load check if day is Monday then peek(amount,-3) else 0 as friday_amt ,
If day is Monday then peek(amount,-4) , 0)as thirsday_amt

So now you created 2 new columns

Use another preceding load to compare the amount with friday_amt and Thursday_amt

the following lines you find them in the beginning of the script:

SET DateFormat='MM/DD/YYYY'; 
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun'; 
SET FirstWeekDay=6; 

this will make monday day 1 ; so if a date is really on monday then the weekday function will return 1 for monday, 2 for tuesday,...

test:
Load *,(WeekDay(_date)) as week_day,num(WeekDay(_date)) as num_week_day
;
Load * Inline [
id,_date,amount
8287,03,14,55
8188,03/21/2024,37.23 
8189,03/20/2024,17.17 
8190,03/19/2024,88.27 
8191,03/18/2024,57.42 
8192,03/17/2024,53.80 
8193,03/16/2024,82.06 
8194,03/15/2024,40.39 
];

Transactions: 
NoConcatenate
Load * ,if(amount>fri_amt,.....;
Load 
* ,if(week_day=1,peek(amount,-3)) as fri_amt
,if(week_day = 1,peek(amount,-4)) as thurs_amt
Resident test
order by [_date] asc;
drop table test;

[Rev2091]

hi, this works perfectly. thank you.

i tried with sat being a workday as well and managed to use or in num(WeekDay(_date)) to create the flag.

just wondering can this be done in a for loop instead of using so many if conditions?