Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Are you using a master calendar?
If not, have a look at
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,
...
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
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
;
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
Worked
Thank you !