Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Compare days of week

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

1 Solution

Accepted Solutions

Re: Compare days of week

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

5 Replies
MVP
MVP

Re: Compare days of week

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

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

Not applicable

Re: Compare days of week

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

;

Re: Compare days of week

Hi,

insert preceding loads before your sql select to generate the MonthWeek, MonthWeekDay and MonthWeekDayName fields like in my example and replace the "Date" field with the appropriate field from your sql source:

TB1900:

LOAD ...

;

sql

select t3.*,

...

hope this helps

regards

Marco

Not applicable

Re: Compare days of week

Worked

Thank you !

Community Browser