Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Have you checked if the selected Month have values available?
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)
Hi Sunny,
this expression is not returning any value at all.
Sarif
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
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)
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
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
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)
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]