Handle weekends and public holidays , consider next working day data Qliksense

[Aspiring_Developer]

Hello All ,

I have below data set :-

Data highlighted with yellow are weekends

Data highlighted with Peach is public holidays.

I have created below logic where we are taking sum(volume) only for the last working day of each month .

At backend -
If(floor(MonthEnd(REPORT_DATE))=REPORT_DATE,1,0) as isLastDayOfMonth,

Front end = Sum({<isLasyDayofMonth={1}>}Volume).

Now, I need to add one more clause here . If the last working day is Saturday , Sunday or Public holiday . So , it shoud display the next opening day value .

For instance, this month closing value of Dec2022(31/12/2022) is 105.
But 31st dec 2022 is saturday , then 1st jan 2023 is Sunday and 2nd jan 2023 is a public holiday .
So , it should display the value of 3rd jan 2022 which is 87 against dec 2022 . So, 3rd jan 2023 value will be considerd as closing month value for previous month.

Can anyone please help me how Can i add this clause in my logic ?

Thanks

[brunobertels · Accepted solution]

hi 

ok 

see below code 

Table:
load
date#(date(num([Report Date]),'DD/MM/YYYY'),'DD/MM/YYYY') as [Report Date],
weekday(date#(date(num([Report Date]),'DD/MM/YYYY'),'DD/MM/YYYY')) as weekday,
if(floor(monthend([Report Date])) = [Report Date],
date(lastworkdate(monthend([Report Date]), 1, $(HolidayList)),'DD/MM/YYYY'),null() ) as FLAG,
[volume] ;

load * inline [
Report Date,volume
44896,10
44897,20
44898,30
44899,40
44900,50
44901,60
44902,70
44903,80
44904,90
44905,100
44906,90
44907,80
44908,70
44909,60
44910,50
44911,40
44912,30
44913,20
44914,10
44915,5
44916,10
44917,15
44918,20
44919,25
44920,30
44921,35
44922,40
44923,45
44924,50
44925,55
44926,60
44927,65
44928,70
44929,75
44930,80
44931,85
44932,90
44933,95
44934,100
44935,105
44936,115
44937,10
44938,20
44939,30
44940,40
44941,50
44942,60
44943,70
44944,80
44945,90
44946,100
44947,90
44948,80
44949,70
44950,60
44951,50
44952,40
44953,30
44954,20
44955,10
44956,5
44957,10
44958,15
44959,20
44960,25
44961,30
44962,35
44963,40
44964,45
44965,50
44966,55
44967,60
44968,65
44969,70
44970,75
44971,80
44972,85
44973,90
44974,95
44975,100
44976,105
44977,115
44978,10
44979,20
44980,30
44981,40
44982,50
44983,60
44984,70
44985,80
44986,90
44987,100
44988,90
44989,80
44990,70
44991,60
44992,50
44993,40
44994,30
44995,20
44996,10
44997,5
44998,10
44999,15
45000,20
45001,25
45002,30
45003,35
45004,40
45005,45
45006,50
45007,55
45008,60
45009,65
45010,70
45011,75
45012,80
45013,85
45014,90
45015,95
45016,100
45017,105
45018,115
45019,10
45020,20
45021,30
45022,40
45023,50
45024,60
45025,70
45026,80
45027,90
45028,100
45029,90
45030,80
45031,70
45032,60
45033,50
45034,40
45035,30
45036,20
45037,10
45038,5
45039,10
45040,15
45041,20
45042,25
45043,30
45044,35
45045,40
45046,45
45047,50
45048,55
45049,60
45050,65
45051,70
45052,75
45053,80
45054,85
45055,90
45056,95
45057,100
45058,105
45059,115
45060,10
45061,20
45062,30
45063,40
45064,50
45065,60
45066,70
45067,80
45068,90
45069,100
45070,90
45071,80
45072,70
45073,60
45074,50
45075,40
45076,30
45077,20
45078,10
45079,5
45080,10
45081,15
45082,20
45083,25
45084,30
45085,35
45086,40
45087,45
45088,50
45089,55
45090,60
45091,65
45092,70
45093,75
45094,80
45095,85
45096,90
45097,95
45098,100
45099,105
45100,115
45101,55
45102,60
45103,65
45104,70
45105,75
45106,80
45107,85
45108,90
45109,95
45110,100
45111,105
] (txt) ;

temp:
noconcatenate
load
FLAG,
[Report Date] as [Report Date_END]
resident Table where FLAG>0 ;

left join (temp)
Load
[Report Date] as FLAG,
[volume] as VolFinMois
resident Table ;

left join (Table)
Load
[Report Date_END] as [Report Date],
VolFinMois
resident temp;

drop table temp;