5 Replies Latest reply: May 10, 2016 3:01 AM by omri levi RSS

    Compare days of week

    omri levi

      Hello,

      I want to compare week-days from this month to the previous.

       

      April:

      SundayMondayTuesdayWednesdayThursdayFrydaySaturday                        
      0102Week number 1
      03040506070809Week number 2
      10111213141516Week number 3
      17181920212223Week number 4
      24252627282930Week number 5

      May:

      SundayMondayTuesdayWednesdayThursdayFrydaySaturday                        
      01020304050607Week number 1
      08091011121314Week number 2
      15161718192021Week number 3
      22232425262728Week number 4
      293031Week number 5

       

      For example:

      I want to compare Friday of week #1 in April (01-April) to Friday of week #1 in May (06-May)

       

      I tried something like this:

       

      =WeekDay(START_TIME)&'-'&(if(month(weekend(START_TIME))= month(START_TIME),div(day(WeekEnd(START_TIME)),7),div(day(Weekend(START_TIME,-1)),7)+1))

       

      Thank you

        • Re: Compare days of week
          Stefan Wühl

          Are you using a master calendar?

           

          If not, have a look at

          The Master Calendar

           

          You could create a week counter per month in your master calendar like

           

          LOAD *,

                    Weekday & '-' WeekMonthNumber as WeekdayWeekMonthKey;

          LOAD *,

                    Autonumber(Week, YearMonth) as WeekMonthNumber;

          LOAD Date,

                    Month(Date) as Month,

                    Year(Date) as Year,

                    MonthName(Date) as YearMonth,

                    Weekday(Date) as Weekday,

                    Week(Date) as Week,

                    ...

          • Re: Compare days of week
            Marco Wedel

            Hi,

             

            one solution might be also:

             

            QlikCommunity_Thread_215695_Pic4.JPG

            QlikCommunity_Thread_215695_Pic3.JPG

            QlikCommunity_Thread_215695_Pic2.JPG

            QlikCommunity_Thread_215695_Pic1.JPG

             

             

             

            tabCalendar:
            LOAD *,
                Dual('W'&MonthWeek&' '&WeekDay,MonthWeekDay) as MonthWeekDayName;
            LOAD *,
                Ceil(MonthWeekDay/7) as MonthWeek,
                Ceil(Rand()*100) as Value;
            LOAD *, 
                Day(Date) as Day,
                WeekDay(Date) as WeekDay,
                Day(Date)+WeekDay(MonthStart(Date)) as MonthWeekDay,     
                Num(WeekDay(Date)) as WeekDayNum,
                Week(Date) as Week, 
                WeekName(Date) as WeekName, 
                Month(Date) as Month, 
                MonthName(Date) as MonthName, 
                Dual('Q'&Ceil(Month(Date)/3),Ceil(Month(Date)/3)) as Quarter, 
                QuarterName(Date) as QuarterName, 
                Year(Date) as Year, 
                WeekYear(Date) as WeekYear;   
            LOAD Date(MinDate+IterNo()-1) as Date 
            While MinDate+IterNo()-1 < MaxDate; 
            LOAD MakeDate(2000) as MinDate, 
                MakeDate(2017) as MaxDate 
            AutoGenerate 1;
            

             

            hope this helps

             

            regards

             

            Marco

              • Re: Compare days of week
                omri levi

                When I try to link this calendar to my other table I get an error. I don't have a lot of experience with qlikview.

                Can you please help?

                Thank you

                 

                 

                TB1900:

                sql

                select t3.*,

                t3.BILLED_AMOUNT - t3.PREMIUM_RATE AS Delta,

                CASE WHEN t3.BILLED_AMOUNT  IS NOT NULL THEN

                  CASE WHEN t3.BILLED_AMOUNT - t3.PREMIUM_RATE <> 0 THEN 'BAD'

                  ELSE 'OK' END

                  ELSE 'EMPTY' END

                  AS IND

                from(

                select t1.*, t2.*

                from

                (

                select c.TO_DATE, c.BILL_REF_NO, SUBSTRING(c.POINT_ORIGIN,len(c.POINT_ORIGIN)-8,9) AS POINT_ORIGIN, c.POINT_TARGET, c.TRANS_DT, round(sum(c.BILLED_AMOUNT)/10000,2) as BILLED_AMOUNT,

                SUBSTRING(c.POINT_TARGET,1,4) AS SHORT_POINT_TARGET, month(c.TRANS_DT) AS TRANS_DT_month, month(c.TO_DATE) AS TO_DATE_month

                from billed_cdr c

                where c.TO_DATE >= '2015-09-01'

                group by c.TO_DATE,c.BILL_REF_NO, SUBSTRING(c.POINT_ORIGIN,len(c.POINT_ORIGIN)-8,9), c.POINT_TARGET, c.TRANS_DT

                ) t1

                RIGHT OUTER JOIN

                (

                select d.NETWORK, d.HOST_OPERATOR, SUBSTRING(d.CALLING_MSISDN,len(d.CALLING_MSISDN)-8,9) as CALLING_MSISDN, d.CALLED_MSISDN,  d.START_TIME, sum(cast(d.PREMIUM_RATE as float)) as PREMIUM_RATE

                ,SUBSTRING(CAST(d.CALLED_MSISDN AS varchar(38)),1,4) AS SHORT_CALLED_MSISDN, month(d.START_TIME) AS start_time_month

                from premium_cdr d

                where 1=1

                group by d.NETWORK, d.HOST_OPERATOR, SUBSTRING(d.CALLING_MSISDN,len(d.CALLING_MSISDN)-8,9), d.CALLED_MSISDN,  d.START_TIME

                ) t2

                ON t1.POINT_ORIGIN = t2.CALLING_MSISDN and t1.TRANS_DT = t2.START_TIME and t1.POINT_TARGET = t2.CALLED_MSISDN

                /* and t1.POINT_ORIGIN = 54748358 */

                ) t3

                ;