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
Weekday & '-' WeekMonthNumber as WeekdayWeekMonthKey;
Autonumber(Week, YearMonth) as WeekMonthNumber;
Month(Date) as Month,
Year(Date) as Year,
MonthName(Date) as YearMonth,
Weekday(Date) as Weekday,
Week(Date) as Week,
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
QlikCommunity_Thread_215695.qvw 252.2 K
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?
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
select t1.*, t2.*
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
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
group by d.NETWORK, d.HOST_OPERATOR, SUBSTRING(d.CALLING_MSISDN,len(d.CALLING_MSISDN)-8,9), d.CALLED_MSISDN, d.START_TIME
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 */