Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

mhmmd_srf
Contributor

Current Month KPI Tiles is not changing

Hi All,

I have two Single KPI tiles in my dashboard.

The expression is below:

MTTR Current Month: Sum({$<Req_state={3},Month_Year_Opened={'$(=$(MaxMonth))'}>}[Duration])*1440/Count({$<Req_state={3},Month_Year_Opened={'$(=$(MaxMonth))'}>}Req_number)

MTTR Rolling 24:

Sum({$<Req_state={3},Month_Year_Opened ={">=$(=Date(AddMonths($(MaxMonth), -24), 'YYYY-MMM'))<=$(=Date(AddMonths($(MaxMonth), -1),  'YYYY-MMM'))"}>} Duration)*1440/Count({$<Req_state={3},Month_Year_Opened ={">=$(=Date(AddMonths($(MaxMonth), -24), 'YYYY-MMM'))<=$(=Date(AddMonths($(MaxMonth), -1),  'YYYY-MMM'))"}>} Req_number)

Below is the Script for Variable:

Last_Month:

Load

  Date(MonthStart(Today(),-RowNo()+1), 'YYYY-MMM') as Prior_Months

AutoGenerate 26;

Set MaxMonth=Max([Prior_Months]);

But when I am selecting value for Month_Year_Opened filter. MTTR Rolling is showing data for the selected Month only and MTTR current Month is showing Blank.

However MTTR Current Month show data for current Month and MTTR Rolling should show value for last 24 months from selected Month.

Please help.

Sarif

9 Replies
MVP
MVP

Re: Current Month KPI Tiles is not changing

Have you checked if the selected Month have values available?

MVP
MVP

Re: Current Month KPI Tiles is not changing

My bad, I think you need to use Date function around your variable:

Sum({$<Req_state={3},Month_Year_Opened={'$(=Date($(MaxMonth), 'YYYY-MMM'))'}>}[Duration])*1440/Count({$<Req_state={3},Month_Year_Opened={'$(=Date($(MaxMonth), 'YYYY-MMM'))'}>}Req_number)

mhmmd_srf
Contributor

Re: Current Month KPI Tiles is not changing

Hi Sunny,

this expression is not returning any value at all.

Sarif

ogster1974
Honored Contributor II

Re: Current Month KPI Tiles is not changing

If you expose in a text box the value in $(MaxMonth) and do your tests I think you will get visibility of your issue.

Regards

Andy

MVP
MVP

Re: Current Month KPI Tiles is not changing

Made some changes, did you try the updated expression? Needed to include the date format also:

Sum({$<Req_state={3},Month_Year_Opened={'$(=Date($(MaxMonth), 'YYYY-MMM'))'}>}[Duration])*1440/Count({$<Req_state={3},Month_Year_Opened={'$(=Date($(MaxMonth), 'YYYY-MMM'))'}>}Req_number)

mhmmd_srf
Contributor

Re: Current Month KPI Tiles is not changing

Yes Andy...you are correct...variable is not changing it is values with change of selection.

Now I changed variable expression to :

set v_MaxMonthReq= max(Month_Year_Opened);

Now other way is happening. Current Month value is showing but Rolling 24 is not working. Though variable is changing its value in Text Box.

Sarif

mhmmd_srf
Contributor

Re: Current Month KPI Tiles is not changing

Sunny I am using below script

Count({$<Req_state={3},Month_Year_Opened ={">=$(=Date(AddMonths($(v_MaxMonthReq), -24), 'YYYY-MMM'))<=$(=Date(AddMonths($(v_MaxMonthReq), -1),  'YYYY-MMM'))"}>} Req_number)

set v_MaxMonthReq= max(Month_Year_Opened);

Now it is showing value in default but when I am selecting any Month_Year_Opened from filter it is showing 0.

Please help.

Sarif

MVP
MVP

Re: Current Month KPI Tiles is not changing

What format is Month_Year_Opened field? Just curious, what happens when you use the Max() function directly? Same issue?

Count({$<Req_state={3},Month_Year_Opened ={">=$(=Date(AddMonths(Max(Month_Year_Opened), -24), 'YYYY-MMM'))<=$(=Date(AddMonths(Max(Month_Year_Opened), -1),  'YYYY-MMM'))"}>} Req_number)

mhmmd_srf
Contributor

Re: Current Month KPI Tiles is not changing

Same issue with this expression also.

This is the expression I used to create Month_Year_Opened field.

Date( MonthStart( ConvertToLocalTime([opened_at], 'Eastern Time (US & Canada)') ), 'YYYY-MMM') as [Month_Year_Opened]