topic Re: Handle weekends and public holidays , consider next working day data Qliksense in App Development

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

Aspiring_Developer — Wed, 11 Jan 2023 10:51:09 GMT

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

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

Aspiring_Developer — Thu, 12 Jan 2023 13:25:08 GMT

Hi ,

Can anyone please help me on this one ?

Thanks

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

brunobertels — Thu, 12 Jan 2023 17:47:02 GMT

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

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

Aspiring_Developer — Fri, 13 Jan 2023 12:36:58 GMT

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

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

Aspiring_Developer — Thu, 19 Jan 2023 10:05:15 GMT

Hello All,

Can anyone please help me with this one ?

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

Thanks

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

barnabyd — Thu, 19 Jan 2023 20:42:19 GMT

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.

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

sidhiq91 — Fri, 20 Jan 2023 02:37:14 GMT

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

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

Aspiring_Developer — Fri, 20 Jan 2023 07:22:03 GMT

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

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

brunobertels — Fri, 20 Jan 2023 09:07:11 GMT

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

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

Aspiring_Developer — Fri, 20 Jan 2023 09:13:22 GMT

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

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

brunobertels — Fri, 20 Jan 2023 10:18:47 GMT

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;