topic Re: Count days between current selection in Master Calendar in QlikView

Count days between current selection in Master Calendar

Thu, 24 Jan 2013 09:30:11 GMT

Hi Guys,

I have a master Calendar setup on my dashboard (Code Below):

(Code Below)

I need to find the number of weekdays days (Monday - Friday inclusive) currently selected on the master calendar in order to use it as part of an expression

Any help would be helpful,

I vaguely remember covering somehting about calculating current selections in Qlikview Training but that was a while back.

Thanks in advance

//**** MASTER CALENDAR****

MINMAX:
LOAD
Min(LoggedDate) as MinDate,
Max(LoggedDate) as MaxDate
RESIDENT Richmond;
LET vMinDate = Num(Peek('MinDate',0,'MinMax'));
LET vMaxDate = Num(Peek('MaxDate',0,'MinMax'));
LET vToday = $(vMaxDate);

//**** Temp Calendar****
TempCal:
LOAD
     date($(vMinDate) + rowno()-1) AS TempDate
     AUTOGENERATE
     $(vMaxDate)-$(vMinDate)+1;
     DROP Table MINMAX;

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

MasterCalendar:
LOAD
TempDate AS LoggedDate,
Week(TempDate) AS Week,
Year(TempDate) AS Year,
Month(TempDate) AS Month,
Day(TempDate) AS Day,
Weekday(TempDate) AS WeekDay,

'Q' & ceil(month(TempDate) / 3) AS Quarter,
DATE(Monthstart(TempDate),'MMM-YYY') AS MonthYear,
Week(TempDate)&'-'&Year(TempDate) AS WeekYear,
InYearToDate(TempDate,$(vToday),0)*-1 AS LastYTDFlag
RESIDENT TempCal
ORDER BY TempDate ASC;

DROP TABLE TempCal;

Re: Count days between current selection in Master Calendar

Gysbert_Wassenaar — Thu, 24 Jan 2013 09:38:29 GMT

Your date field is named LoggedDate as far as I can tell. So you want the number of weekdays between the minimum selected LoggedDate and the maximum selected LoggedDate. Right?

This should calculate the number of working days between the min and max selected LoggedDate: networkdays(min(LoggedDate),max(LoggedDate))

Re: Count days between current selection in Master Calendar

Thu, 24 Jan 2013 11:54:20 GMT

Hi Gysbert,

Thank you for this, It is very nearly there.

It calculates perfectly when there is nothing selected.

Also works when I select any time period.

however,

when I make a selection in another list it throws off the figure.

The other list is 'Specialist' and contains names.

I think when I select an individual name it is counting network days for ONLY days that specialist has logged records for.

Is there a way to get around this? I need the calculation to be locked to the days between the master calendar selection so other selections do not impact it.

Many thanks

Re: Count days between current selection in Master Calendar

Gysbert_Wassenaar — Thu, 24 Jan 2013 12:57:04 GMT

I think you'll have to use two calendar objects that each set a variable and use the variable in the networkingdays function. That way you can select a start and end date with the calendar objects and have the expression return the number of working days between the start and end date. You can also use the same objects to select a date range in your LoggedDate field. See here for a tutorial about this subject.

Re: Count days between current selection in Master Calendar

sebastiandperei — Thu, 24 Jan 2013 13:21:28 GMT

Hi!

If I understood, you need to count non-continous workdays of a selection. You can add the following line in MasterCalendar:

If( WeekDay(TempDate)<5, 1) as _Work_Day_Flag

Then, your expression could be:

Sum (_Work_Day_Flag), and it will give you the number of working days with every selection. This is the easiest way, because the expression is very simple.

If you don't want to change the script, the expression could be:

Sum( If ( WeekDay (TempDate) <5, 1 ) )

Try

Re: Count days between current selection in Master Calendar

Thu, 24 Jan 2013 14:00:40 GMT

Thank you,

I had a look at the _Work_Day_Flag method which seems to make sense.

It calculates fine when no selection is made.

Calculates fine when a date selection is made.

However when I select a specialist the number it throws back is not correct.

I am looking at December 2012 (21 Weekdays)

The dates that are logged against the selected Specialist Specialist are as follows, This method returns 14 when it should return 21:

Tue 04-Dec-12

Fri 07-Dec-12

Mon 10-Dec-12

Tue 11-Dec-12

Wed 12-Dec-12

Thu 13-Dec-12

Fri 14-Dec-12

Tue 18-Dec-12

Wed 19-Dec-12

Thu 20-Dec-12

Mon 24-Dec-12

Thu 27-Dec-12

Fri 28-Dec-12

Mon 31-Dec-12

I also tried the method using the expression and the TempDate field but it locked the number to count weekdays in the entire dataset without reflecting the selections made in the Master Calendar.

Re: Count days between current selection in Master Calendar

sebastiandperei — Thu, 24 Jan 2013 14:19:27 GMT

I see you need to count the repeated days as different days.... that is correct?

TempDate is key field between MasterCalendar and success table?

Re: Count days between current selection in Master Calendar

Thu, 24 Jan 2013 14:27:09 GMT

The days that are listed above are the dates that a particular specialist has logged. (Fieldname: Loggeddate)

in a way, these are irrelevant as I am only looking for the number of working days reflected in the date selection. So if December is selected it will count all weekdays in December, If a year is selected it will count all weekdays in that year.

From what I remember the TempDate is a field used in the master calendar to hold the lowest date, the code below is what assigns this field, however this table iss dropped at the end of the master calendar script.

Hope this helps.

//**** Temp Calendar****
TempCal:
LOAD
     date($(vMinDate) + rowno()-1) AS TempDate
     AUTOGENERATE
     $(vMaxDate)-$(vMinDate)+1;
     DROP Table MINMAX;

Re: Count days between current selection in Master Calendar

Thu, 24 Jan 2013 15:02:35 GMT

Thanks Gysbert, I will access this link as soon as the IT department unrestrict youtube! I will let you know if solves it for me.

Re: Count days between current selection in Master Calendar

sebastiandperei — Thu, 24 Jan 2013 16:13:17 GMT

In this list, you have only 14 days, not 21. If you want to get 21, that isn't the "number of working days".

If you want to make the expression "doesn't care" about specialist selection (in the list, it gives you only the working days that your selections are related to) you could use:

sum({<specialist=>}_Work_Day_Flag)

Sum( {<specialist=>} If ( WeekDay (TempDate) <5, 1 ) )

Re: Count days between current selection in Master Calendar

Thu, 24 Jan 2013 16:19:54 GMT

Sebastian!

BINGO!

sum({<specialist=>}_Work_Day_Flag)

worked perfectly!

It now 'locks' the calculation down to the selected dates just like I wanted!

Thank you soooo much!