Announcements
The way to achieve your own success is the willingness to help somebody else. Go for it!
cancel
Showing results for
Did you mean:
Creator III

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

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.

Thanks

Labels (1)
• ### General Question

1 Solution

Accepted Solutions
Master

hi

ok

see below code

Table:
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] ;

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
FLAG,
[Report Date] as [Report Date_END]
resident Table where FLAG>0 ;

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

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

drop table temp;

10 Replies
Creator III
Author

Hi ,

Thanks

Master

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

Creator III
Author

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

Creator III
Author

Hello All,

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

Thanks

Partner - Creator II

G'day @Aspiring_Developer,

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

1. 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
2. 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
Specialist II

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

Creator III
Author

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

Master

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

Creator III
Author

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

Tags
Community Browser