topic Re: Working Day in QlikView

Working Day

qlikviewaf — Wed, 25 Nov 2020 16:16:04 GMT

Hi Guys,

i created a dashboard and now i'm working on Working Days - i added a flag on my Calendar Table , if equal to 1 is a working day if equal to 0 is non working day.

As you can see i would like to show for each region the total working day for that specific year/month; as you can see below the system is calculating the working day only if for that specific month there was a sale transaction every day. Total should be always 22 for all rows/region.

I set the expression as -> =sum({<Day=,Region=,Region_Description=,Area=,Invoice_Date=,Invoice_Date_Num=>}WorkingDay)

What can i do to fix this? Thank you. !

Find attached also my app.

Re: Working Day

rahulpawarb — Tue, 01 Aug 2017 13:33:12 GMT

May be this:

=NetWorkDays(MonthStart(MakeDate(2017, Num(Month(Date#(GetFieldSelections(Month), 'MMMM'))), 1)), MonthEnd(MakeDate(2017, Num(Month(Date#(GetFieldSelections(Month), 'MMMM'))), 1)))

Regards!

Rahul Pawar

Re: Working Day

qlikviewaf — Wed, 02 Aug 2017 10:33:40 GMT

Ehy Rahul, this is working but i would like to use the WorkingDay since is part of my calendar.

i used Sum({<Day=,Invoice_Date=,Invoice_Date_Num=,Region=,Region_Description>}WorkingDay) but qlikview is considering the day only if there was an invoice for that specific day, i would like to have the total of working day for the month/year selected regardless the invoice done on a specific month.

Re: Working Day

rahulpawarb — Wed, 02 Aug 2017 12:32:46 GMT

Hello Antonio,

As a workaround, please create a variable vWorkingDays with definition =Sum(WorkingDay) . Later change the definition of Working Day expression to =vWorkingDays .

Hope this will help.

Regards!

Rahul Pawar

Re: Working Day

qlikviewaf — Wed, 02 Aug 2017 12:38:27 GMT

not working

Re: Working Day

rahulpawarb — Wed, 02 Aug 2017 12:53:43 GMT

Please refer attached application.

Regards!

Rahul Pawar

Re: Working Day

qlikviewaf — Wed, 02 Aug 2017 14:16:39 GMT

But then if i select a day the working day goes to 0.

I need the total working day for the month/year regardless the fact that i selected a specific day; i need to calculate this in order to split than the budget of the month for the number of working days.

What can i do? Thank you for your time

Re: Working Day

rahulpawarb — Thu, 03 Aug 2017 04:12:20 GMT

To fix this issue you can change the definition of variable vWorkingDays with =Sum({<Day=>}WorkingDay)

to exclude selections made on Day list box.

Hope this will help.

Regards!

Rahul Pawar

Re: Working Day

qlikviewaf — Thu, 03 Aug 2017 06:50:38 GMT

at the end i used and it worked

=sum({<Quarter=, Day=,Invoice_Date=,Area=,Region=,Region_Description,Product_Line=,Item_Category=>} WorkingDay) since the selection is done also on Region which is one of the dimension of the table.

now new challange: i have to sum till the day selected, which means if i select 14th as day, i would like to sum the total working day from 1th till 14th. Which is the syntax in this case?

Thank you!!

Re: Working Day

avinashelite — Thu, 03 Aug 2017 07:11:23 GMT

Two ways to achieve this

1.remove the Day from the set analysis

2.Create a vStartDate and vEndDate Variables and set the vStartDate as start of the date selected i.e monthstart(max(day)) similar set the end date