Over Time Comparissos

[dries_clairhout]

Hello everyone,

I need to put a month to month year prior comparrison for the vColli dimension at my client's company. this is the master calendar they built before my time:

min_max:
Load
min(%Kalender) as minDate,
max(%Kalender) as maxDate
Resident Picking;

Let vMinDate = Peek('minDate', 0, 'min_max')-1;
Let vMaxDate = Peek('maxDate', 0, 'min_max');
Let vGenerate = round(Peek('maxDate', 0, 'min_max') - $(vMinDate));

DROP Table min_max;


Kalender:
Load *,
TempDate AS %Kalender,
Year(TempDate)&Ceil (Month(TempDate)/3) as JaarKwartaal,
date(TempDate) As Datum,
week(TempDate) As Week,
Year(TempDate) As Jaar,
Month(TempDate) As Maand,
num(month(TempDate), '00' ) As Periode,
Day(TempDate) As Dag,
date(monthstart(TempDate), 'MMM-YYYY') As MaandJaar,
num#(date(monthstart(TempDate), 'YYYYMM') ) As JaarPeriode,
(Year(TempDate) * 12) + num(month(TempDate), '00' ) as JaarPeriodeID,
'Q' & Ceil (Month(TempDate)/3) As Kwartaal,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) As WeekJaar,
WeekDay(TempDate) As WeekDag,
if(TempDate = $(vMaxDate)
, 1
) As MaxDateFlag,
if(MakeDate(2000, month(today()), day(today())) >= MakeDate(2000, month(TempDate), Day(TempDate))
, 'YTD'
) As YTD
;

LOAD
$(vMinDate) + RecNo() As TempDate
autoGenerate $(vGenerate);

 Does anyone have any ideas,

how i shuld go about this?

Thanks in advance.

[Uday_Pasupuleti]

HI,

Do you want to compare Current Year Month to Date vs Last Year Month to Date?

[dries_clairhout]

Hello,

indeed, the client wants to see the following:

1) when selecting a month, he wants to see the number of the month year priot next to the current numbers to compare.

2) when selecting for example 2018 he wants to see the same data from the year before next to it.

We use this in our qlik sense enviroment:

[Uday_Pasupuleti]

In that case just use the set analysis in your measure expression.
1. With max(Year)
2. With max(Year)-1

Or you can create flags in your calendar
Firs create a variable for today.
Let vToday = num(Today())

Then in your master calendar create the following flags
InYearToDate(TempDate,$(vToday),0) * -1 as CurYTDFlag,
//YeartoDate((TempDate-1),-1)*-1 as LastYTDFlag,
inmonthtodate(TempDate,$(vToday),0) * -1 as CurMTDFlag,
InWeekToDate(TempDate,$(vToday),0) * -1 as CurWTDFlag,
inmonthtodate(TempDate,$(vToday),+1) * -1 as NextMTDFlag,
inmonthtodate(TempDate,$(vToday),-1) * -1 as LastMTDFlag,
If( InMonth (TempDate, $(vToday),0),1) as This_Month_Flag,
If( InMonth (TempDate, $(vToday),-1),1) as Last_Month_Flag,
If( InMonth (TempDate, $(vToday),-12),1,0) as LYMTDFlag,
if(date(TempDate) >= Date(AddMonths(Today(0),-12)), '1') as Rolling12Mo,
if(date(TempDate) >= Date(Today(0)-30), '1') as Rolling30Day,
etc..

[dries_clairhout]

Hello,

the variable is made and I scripted this:

But tis is giving an error behind "as weekDag" expected ")"

Don't know how this gives an error.

Any ideas?

[Uday_Pasupuleti]

Hi,

Try this.I have tested it and working fine.


Let vToday = num(Today()-1);

SET vFiscalYearStartMonth = 4;


QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);

MasterCalendar:
Load
//TempDate AS "Post Date",
Date(TempDate,'DD/MM/YYYY') as Date,
//'WK'& week(TempDate) as Week,
//week(TempDate) as WeekNo,

Year(YearName(TempDate, 0, $(vFiscalYearStartMonth)))+1 AS FYYear,
Dual(Month(TempDate),Mod(Month(TempDate) - $(vFiscalYearStartMonth), 12)+1) as FYMonth,
Dual('Q' & Ceil((Mod(Month(TempDate) - $(vFiscalYearStartMonth), 12)+1)/3), Ceil((Mod(Month(TempDate) - $(vFiscalYearStartMonth), 12)+1)/3)) as FYQuarter,
Ceil((TempDate-WeekStart(YearStart(TempDate,0,$(vFiscalYearStartMonth)))+1)/7) as FYWeekNo,
YearToDate(TempDate,0,$(vFiscalYearStartMonth))*-1 as FYCYYTD,
YearToDate((TempDate),-1,$(vFiscalYearStartMonth))*-1 as FYLYYTD,


Year(TempDate) As Year,
Month(TempDate) As Month,
Num(Month(TempDate)) As MonthNum,
If( DayNumberOfYear(TempDate) <= DayNumberOfYear(Today()), 1, 0 ) as IsInYTD,
//YeartoDate(TempDate)*-1 as CurYTDFlag,

InYearToDate(TempDate,$(vToday),0) * -1 as CurYTDFlag,
//YeartoDate((TempDate-1),-1)*-1 as LastYTDFlag,
inmonthtodate(TempDate,$(vToday),0) * -1 as CurMTDFlag,
InWeekToDate(TempDate,$(vToday),0) * -1 as CurWTDFlag,
inmonthtodate(TempDate,$(vToday),+1) * -1 as NextMTDFlag,
inmonthtodate(TempDate,$(vToday),-1) * -1 as LastMTDFlag,
If( InMonth (TempDate, $(vToday),0),1) as This_Month_Flag,
If( InMonth (TempDate, $(vToday),-1),1) as Last_Month_Flag,
If( InMonth (TempDate, $(vToday),-12),1,0) as LYMTDFlag,
if(date(TempDate) >= Date(AddMonths(Today(0),-12)), '1') as Rolling12Mo,
if(date(TempDate) >= Date(Today(0)-30), '1') as Rolling30Day;


Load
AddMonths(today(),12) - recno() AS TempDate
Autogenerate (AddMonths(today(),12) - MakeDate(2016,01,01));

[dries_clairhout]

Hello,

first of all, thank you for the responses.

Secondly:

QuarterMAP?

I don't know what you mean by that.

Let vToday = num(Today()-1);

SET vFiscalYearStartMonth = 4;

Is this both in the same variable?

I'm still getting syntax errors.

Sorry for the many questions.

[Uday_Pasupuleti]

Hi,
Quarter map is to create quarters like Q1,Q2,Q3 and Q4.

vFiscalYearStartMonth is used to create Fiscalyear.

If you don't want them, then you can comment them out.

You need to use vToday variable for the flags.

What are the syntax errors you are getting?

Please