Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

How to split working days month and Year wise in pivot table

Hi All,

Please advice me. In one expression how to split working days per year and month. For example below is screen shot.

When i use networkdays in expression, days are not divided by month and year together. Aggregated value 1071 has shown for all years. Thanks to advice.

Regards,

Krishna Chaitanya B

16 Replies
maxsheva
Creator II
Creator II

Hi, it depends on data model.

Would you provide data example as well as expected output?

Anonymous
Not applicable
Author

Hi, I Have master calendar data dimension from 2000 to 2100 years.

sunny_talwar

Anonymous
Not applicable
Author

You could probably do it with some nested Ifs, by hardcoding the working days values, and naming setting the expression labels as the years that you're looking for. Are the months given as a dimension in the pivot table?

maxsheva
Creator II
Creator II

Below is a calendar for period as in your example. You may change it in varMinDate, varMaxDate variables.

Use this expression in pivot chart to get hope you need.

NetWorkDays(min(Date),max(Date))

//*************Master Calendar************

LET varMinDate = Num('2016-01-01');

LET varMaxDate = Num('2018-12-31');

TempCalendar:

LOAD      $(varMinDate) + rowno() - 1 AS DateNumber,

               date ($(varMinDate) + rowno() - 1) AS TempDate

AUTOGENERATE $(varMaxDate) - $(varMinDate) + 1;

MasterCalendar:

LOAD         TempDate AS Date,   

               Year (TempDate) AS Year,

               Month (TempDate) AS Month

Resident TempCalendar

Order by TempDate ASC;

Drop Table TempCalendar;

Anonymous
Not applicable
Author

Yes, Months are given as dimension in straight table. Three years are hard coded.

Anonymous
Not applicable
Author

Max, due to this script and expression, same value is displayed in straight table. expecting data pasted into excel and image attached.

My expectation is, YTD working days for all three years.

Anonymous
Not applicable
Author

Maybe something like:

if(month = 'January',16

,

if(month = 'February',20

,

if(month = 'March',23

,

if(month = 'April',18

,

if(month = 'May',22

,

if(month = 'June',22

,

if(month = 'July',21

,

if(month = 'August',22

,

if(month = 'September',21

,

if(month = 'October', 22

,

if(month = 'November', 21

,

if(month = 'December', 18)

)

))))))))))

Of course, you would have to replace the values for each months. To get the years, you can just put the expression label's name as the year name

Anonymous
Not applicable
Author

Nested IFs for your output:

//For 2016 expression

if(month = 'Jan',19

,

if(month = 'Feb',20

,

if(month = 'Mar',23

,

if(month = 'Apr',21

,

if(month = 'May',21

,

if(month = 'Jun',22

,

if(month = 'Jul',20

,

if(month = 'Aug',23

,

if(month = 'Sep',21

,

if(month = 'Oct', 21

,

if(month = 'Nov', 22

,

if(month = 'Dec', 22)

)

))))))))))

//For 2017 expression

if(month = 'Jan',22

,

if(month = 'Feb',20

,

if(month = 'Mar',23

,

if(month = 'Apr',20

,

if(month = 'May',22

,

if(month = 'Jun',22

,

if(month = 'Jul',20

,

if(month = 'Aug',23

,

if(month = 'Sep',20

,

if(month = 'Oct', 22

,

if(month = 'Nov', 21

,

if(month = 'Dec', 20)

)

))))))))))

//For 2018 expression

if(month = 'Jun',22

,

if(month = 'Feb',20

,

if(month = 'Mar',21

,

if(month = 'Apr',20

,

if(month = 'May',21

,

if(month = 'Jun',21

,

if(month = 'Jul',22

,

if(month = 'Aug',22

,

if(month = 'Sep',20

,

if(month = 'Oct', 23

,

if(month = 'Nov', 22

,

if(month = 'Dec', 19)

)

))))))))))