MTD Analysis KPI

[qlikapple]

HI All,

 I have One requirement,Please find the attached qvw

Requirement is

The data is up to August 2020.  So if the user opens the dashboard   by default in the KPI-Vol () the calculation will be  Last month-last lastmonth/lastlastmonth

example:if the data is august , while opening the dashboard ,it has to show (july data- june data)/ June data.

 and now comes tricky part. if the user selects  may 2020 month, the KPI-Vol ()  has to show as

(May data-April data)/(April data)..

Can anyone please help as it is very urgent . Please find the attached doc

[rushikale0106]

Hi @qlikapple ,

I tried to solve your query in my system. I dint reach to the complete solution. But I created the expressions which will give you the current month when the dashboard is opened and after the selection, it will give you the last month and Last Last Month. I stored these expressions under variables .

Try to work around these expressions. I am trying it too.

vMonth:: =if(isnull(GetFieldSelections(Calender_Month_Name)), Month(Now()), GetFieldSelections(Calender_Month_Name))

vLast_Month:: =month(AddMonths(makedate(2020,(Num(Month(Date#(vMonth, 'MMMM'))))), -1))

vLast_Last_Month::  =month(AddMonths(makedate(2020,(Num(Month(Date#(vMonth, 'MMMM'))))), -2))

The first variable expression will give you the current month or the month selected from the filter.

Try it out. I will post again if I reach the final solution.

Regards,

Rushi

[sunny_talwar]

What happen when the user clears the selection? Does it go back to (july data- june data)/ June or does it stay on (May data-April data)/(April data)?

[edwin]

One way to accomplish this is build a bridge between a calendar table and your fact.  allow the user to select from calendar table.  the bridge will associate the date in the calendar table to three months of data in the Fact table: the current month, prior month, and two months back.  you can then control your expression by adding criteria in set analysis CurrentMonth={1}, or PriorMonth={1}, or TwoMonthsBack={1}.

you can further refine this is all you need is link to the whole month (in fact table add a monthstart and link that to the bridge).  good luck.

Calendar:
LOAD distinct
DECISION_DATE as Calendar_Date,
DEC_DATE,
DECI_DATE,
Date,
Year,
Month,
MonthYear,
MonthYears,
MonthYear123,
weeks,
Weekdays
Resident
TABLE_mod
;

drop fields DEC_DATE, DECI_DATE, Date, Year, Month, MonthYear, MonthYears, MonthYear123, weeks, Weekdays from TABLE_mod;

NoConcatenate
tmpBridge:
load
distinct Calendar_Date
Resident
Calendar
;

left join (tmpBridge)
load
Calendar_Date as DECISION_DATE
Resident
Calendar
;

NoConcatenate
Bridge:
load
DECISION_DATE,
Calendar_Date,
if(monthstart(DECISION_DATE) = monthstart(Calendar_Date),1) as CurrentMonth,
if(monthstart(DECISION_DATE) = addmonths(monthstart(Calendar_Date),-1),1) as PriorMonth,
if(monthstart(DECISION_DATE) = addmonths(monthstart(Calendar_Date),-2),1) as TwoMonthsBack
resident
tmpBridge
;

drop table tmpBridge;

NoConcatenate
NewBridge:
load
*
Resident
Bridge
where CurrentMonth = 1 or PriorMonth=1 or TwoMonthsBack=1;

drop table Bridge;

[Brett_Bleess]

See related post: https://community.qlik.com/t5/New-to-QlikView/MTD-Cal/m-p/1742496#M392761

Regards,
Brett