YTD/MTD/QTD/Rolling 3/Rolling 12 months in filter pane Qlik Sense

[vikasmahajan]

Hi Team,

I am looking for following solution I wanted to display  YTD/MTD/QTD/Rolling 3 months / Rolling 12 months in filter pane 

In measures i wanted to calculate growth% & market share following are the sample data.

Sample_Int:
LOAD * INLINE [
Month, Sales
Jan-09, 55
Feb-09, 39
Mar-09, 33
Apr-09, 56
May-09, 55
Jun-09, 70
Jul-09, 57
Aug-09, 29
Sep-09, 34
Oct-09, 41
Nov-09, 12
Dec-09, 11
Jan-10, 24
Feb-10, 85
Mar-10, 99
Apr-10, 77
May-10, 85
Jun-10, 64
Jul-10, 61
Aug-10, 45
Sep-10, 62
Oct-10, 70
Nov-10, 59
Dec-10, 25
Jan-11, 86
Feb-11, 92
Mar-11, 67
Apr-11, 65
May-11, 39
Jun-11, 70
];

I tried with diff approaches but no luck . can any one have this logic please share your thoughts.

Measures : 

S.no	Measure
1	%Growth Vs PY = (Current Value - Prior Value)/Prior Value*100
2	% Market Share = (Total sales of the company/ Total sales of the market)*100

Thanks in adv

Vikas

[vinieme12]

group your periods in a separate table, then use the Period field as a filter

https://community.qlik.com/t5/QlikView-Documents/Period-Presets-Compare-Periods-on-the-fly/ta-p/1486...

[vinieme12]

using your sample

Sample_Int:
LOAD *,Monthstart(Date#(Month,'MMM-YY')) as tDate INLINE [
Month, Sales
Jan-09, 55
Feb-09, 39
Mar-09, 33
Apr-09, 56
May-09, 55
Jun-09, 70
Jul-09, 57
Aug-09, 29
Sep-09, 34
Oct-09, 41
Nov-09, 12
Dec-09, 11
Jan-10, 24
Feb-10, 85
Mar-10, 99
Apr-10, 77
May-10, 85
Jun-10, 64
Jul-10, 61
Aug-10, 45
Sep-10, 62
Oct-10, 70
Nov-10, 59
Dec-10, 25
Jan-11, 86
Feb-11, 92
Mar-11, 67
Apr-11, 65
May-11, 39
Jun-11, 70
];

let vBaseDate = date#('2009-05-01','YYYY-MM-DD'); //-- simulating basedate as May-2009 to test ytd and mtd flag, in production should use maxdate

Calendar:
Load
TempDate as tDate
,Date(TempDate-0) as d_Date
,InYearToDate(TempDate, '$(vBaseDate)',0,1)*-1 as YTD_2009
, InMonthToDate(TempDate, '$(vBaseDate)' ,0)*-1 as MTD_2009
//,maxdate <-- use this for ytd, mtd flags
;
LOAD
FieldValue('tDate', recno()) as TempDate
AUTOGENERATE FieldValueCount('tDate');

Period:
CrossTable(Period,FlagValue,2)
Load * Resident Calendar;

Inner Join(Period)
Load * Inline [
FlagValue
1
];

Concatenate(Period)
//Rolling3
Period:
Load
tDate as d_Date
,monthstart(tDate , 1 - IterNo())as tDate
,'Rolling 3' as Period
,1 as FlagValue
Resident Calendar
while IterNo()<=3;

//Rolling12
Load
tDate as d_Date
,monthstart(tDate , 1 - IterNo()) as tDate
,'Rolling 12' as Period
,1 as FlagValue
Resident Calendar
while IterNo()<=12;

drop table Calendar;

exit Script;

In your charts using "d_date" as Dimension

[vikasmahajan]

Hi Vineeth,

Thanku so much for your reply let me try & will close once i tested with my data.

[vikasmahajan]

Hi Vineeth,

 I take max date from this sample and try  ytd/mtd calculation is correctly giving nos however rolling 3 M/ 6M giving me same values pfa attached QVF.

can check please

[vikasmahajan]

I also need to calculate values for previous year for following measures

Measures : 

S.no	Measure
1	%Growth Vs PY = (Current Value - Prior Value)/Prior Value*100
2	% Market Share = (Total sales of the company/ Total sales of the market)*100

[vinieme12]

Hi Vikas,

Unfortunately I don't have access to open qvf files

However, Rolling3/Rolling12 will give you last3 month/last 12 month cumulative sum for each D_date

for example  d_date =  Oct-09 will cumulative Sales from Aug-9 to Oct-9

if you just Need Last3Months and Last 12 months, then just add new flag fields in calendar

Calendar:
Load
TempDate as tDate
,Date(TempDate-0) as d_Date
,InYearToDate(TempDate, maxdate,0,1)*-1 as YTD_2009
, InMonthToDate(TempDate, maxdate ,0)*-1 as MTD_2009
,if(TempDate>=MonthStart(maxdate,-3),1,0) as Last3Month
,if(TempDate>=MonthStart(maxdate,-12),1,0) as Last12Month
,maxdate
;
LOAD
date(MonthStart(mindate ,IterNo()-1)) AS TempDate
,maxdate
WHILE monthstart(mindate , IterNo()-1) <= maxdate;
LOAD
date(min(FieldValue('tDate', recno()))) as mindate
,date(max(FieldValue('tDate', recno()))) as maxdate
AUTOGENERATE FieldValueCount('tDate');

Period:
CrossTable(Period,FlagValue,2)
Load * Resident Calendar;

Inner Join(Period)
Load * Inline [
FlagValue
1
];

Last 3 / 12 monthsRolling 3 / 12 months usage

[vikasmahajan]

Hi Vineeth,

How to create flags for previous year same period  for above flags

,InYearToDate(TempDate, maxdate,0,1)*-1 as YTD_2009
, InMonthToDate(TempDate, maxdate ,0)*-1 as MTD_2009
,if(TempDate>=MonthStart(maxdate,-3),1,0) as Last3Month
,if(TempDate>=MonthStart(maxdate,-12),1,0) as Last12Month

Thanks

Vikas

[vinieme12]

using addyears()

,if(TempDate>=MonthStart(addyears(maxdate,-1),-3) and TempDate<=MonthStart(addyears(maxdate,-1)),1,0) as PriorYear_Last3Month
,if(TempDate>=MonthStart(addyears(maxdate,-1),-12) and TempDate<=MonthStart(addyears(maxdate,-1)),1,0) as PriorYear_Last12Month

[vikasmahajan]

Thanks for your reply one more thing required previous Year YTD &  Previous Year MTD Same period

Current Year 

,InYearToDate(TempDate, maxdate,0,1)*-1 as YTD_2009
, InMonthToDate(TempDate, maxdate ,0)*-1 as MTD_2009

Same period previous Year ?

PRV_YTD

PRV_MTD

?

Thanks

Vikas