Compare days of week

[#1]

Hello,

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

April:

Sunday	Monday	Tuesday	Wednesday	Thursday	Fryday	Saturday
					01	02		Week number 1
03	04	05	06	07	08	09		Week number 2
10	11	12	13	14	15	16		Week number 3
17	18	19	20	21	22	23		Week number 4
24	25	26	27	28	29	30		Week number 5

May:

Sunday	Monday	Tuesday	Wednesday	Thursday	Fryday	Saturday
01	02	03	04	05	06	07		Week number 1
08	09	10	11	12	13	14		Week number 2
15	16	17	18	19	20	21		Week number 3
22	23	24	25	26	27	28		Week number 4
29	30	31						Week 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

[swuehl]

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,

          ...

[MarcoWedel · Accepted solution]

Hi,

one solution might be also:

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

[#4]

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

;

[MarcoWedel]

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

[#6]

Worked

Thank you !