10 Replies Latest reply: Jan 24, 2013 11:19 AM by leviwong RSS

    Count days between current selection in Master Calendar

      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

       

      L

       

       

       

      //**** 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

          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
            Sebastian Pereira

            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

                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

                Wed 12-Dec-12

                Wed 12-Dec-12

                Wed 12-Dec-12

                Wed 12-Dec-12

                Thu 13-Dec-12

                Thu 13-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

                Fri 28-Dec-12

                Mon 31-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.