topic Over Time Comparissos in Water Cooler

Over Time Comparissos

dries_clairhout — Mon, 26 Nov 2018 09:39:20 GMT

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.

Re: Over Time Comparissos

Uday_Pasupuleti — Mon, 26 Nov 2018 09:48:20 GMT

HI,

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

Re: Over Time Comparissos

dries_clairhout — Mon, 26 Nov 2018 09:53:29 GMT

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:

Re: Over Time Comparissos

Uday_Pasupuleti — Mon, 26 Nov 2018 10:03:42 GMT

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..

Re: Over Time Comparissos

dries_clairhout — Mon, 26 Nov 2018 10:28:45 GMT

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?

Re: Over Time Comparissos

Uday_Pasupuleti — Mon, 26 Nov 2018 10:43:44 GMT

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));

Re: Over Time Comparissos

dries_clairhout — Tue, 27 Nov 2018 08:26:27 GMT

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.

Re: Over Time Comparissos

Uday_Pasupuleti — Tue, 27 Nov 2018 08:54:10 GMT

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