Solved: Re: Handle weekends and public holidays , consider... - Qlik Community

Aspiring_Developer · ‎2023-01-11

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 · ‎2023-01-20

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;

View solution in original post

Aspiring_Developer · ‎2023-01-12

Hi ,

Can anyone please help me on this one ?

Thanks

brunobertels · ‎2023-01-12

Hi

Just a test only with one date 31/12/2022 with lastworkdate fonction give the desired date :

LastWorkDate('31/12/2022',1,'01/01/2023','02/01/2023')

then may be with

lastwordate(monthend(Yourdate), 1 , your holidays date ) should work

not tested on whole calendar date

Aspiring_Developer · ‎2023-01-13

Hi @brunobertels

Thanks for your response.

I understand we can make use of last work date. However, it is not clear to me how should I use it precisely in my sample code above.

Thanks

Aspiring_Developer · ‎2023-01-19

Hello All,

Can anyone please help me with this one ?

I tired but did not find any approach to create it.

Thanks

barnabyd · ‎2023-01-19

G'day @Aspiring_Developer,

I don't have a full answer, but I have a couple of ideas that might help:

I think the logic would be simplified if you look for the first working day of each month and then apply that volume to the previous month
Here is a link to a discussion on finding the first working day. If you can get this working, then you can add in the public holidays.

I hope this is useful.

Cheers, Barnaby.

Barnaby Dunn
BI Consultant

sidhiq91 · ‎2023-01-19

@Aspiring_Developer Do you any other column in your table, which says if particular date is public holiday or not?

Aspiring_Developer · ‎2023-01-20

Hello @sidhiq91

No, I don't have a specific column in my data set . Though I have the list of holidays provided to me .

Thanks

brunobertels · ‎2023-01-20

Hi

See attached qvf and excel for sample data ( volume from 01/12/2022 till 04/07/2023 )

here the script I used

1 load table with a flag using lastworkdate function

2 load a temp table with only monthend date and Flag date

3 then left join on first table volume found for Flag Date regarding this time the end of month date

4 left join this end month Volume found on last day of month

Hope it helps

Aspiring_Developer · ‎2023-01-20

Hi @brunobertels

Thanks for your response. But I can't upload external QVF in my Qliksense due to access restriction.

Could you please share the script here ?

Thanks

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

General Question