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

[Anonymous]

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

[maxsheva]

Hi, it depends on data model.

Would you provide data example as well as expected output?

[Anonymous]

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

[sunny_talwar]

May be look here

Generating Missing Data In QlikView‌

and/or this

Loops in the Script

[Anonymous]

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]

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]

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

[Anonymous]

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]

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]

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)

)

))))))))))