Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mhmmd_srf
Creator II
Creator II

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
sunny_talwar

Have you checked if the selected Month have values available?

sunny_talwar

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
Creator II
Creator II
Author

Hi Sunny,

this expression is not returning any value at all.

Sarif

ogster1974
Partner - Master II
Partner - Master II

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

sunny_talwar

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
Creator II
Creator II
Author

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
Creator II
Creator II
Author

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

sunny_talwar

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
Creator II
Creator II
Author

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]