topic Re: MTD Analysis KPI in QlikView

MTD Analysis KPI

qlikapple — Sat, 16 Nov 2024 18:07:43 GMT

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

Re: MTD Analysis KPI

rushikale0106 — Thu, 10 Sep 2020 14:42:35 GMT

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

Re: MTD Analysis KPI

sunny_talwar — Thu, 10 Sep 2020 15:44:42 GMT

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

Re: MTD Analysis KPI

edwin — Thu, 10 Sep 2020 19:10:00 GMT

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;

Re: MTD Analysis KPI

Brett_Bleess — Wed, 23 Sep 2020 11:09:31 GMT

Regards,
Brett