I’m looking to track three key performance indicators (KPIs) related to open issues in a year. The first KPI is the total number of open issues throughout the year. The second KPI specifically focuses on the selected quarter (Q4 in your example). Finally, the third KPI represents the sum of open issues in the periods preceding the selected quarter. Example, if I choose Q3, the third KPI would encompass the combined total of open issues in Q1 and Q2. I’m not able to do third KPI, can you please suggest possible solutions.
Hello @hellosai321, how are you filtering the KPI that displays the open issues in the selected quarter? I’d imagine you could filter the last KPI the same way but on the filter it should be Does not equal instead of Does equal. That will depend on how the filter is built though and I know date filter function differently.
If that doesn’t work, can you explain how you are setting the filter for the 2nd KPI?
I’ve implemented controls(Year and Quarter) driven by parameters to monitor key metrics. The first KPI provides the total count based on specified filters. The second KPI focuses on the count of records with an “open” status, specifically for the selected criteria (e.g., year 2023 and Quarter Q3). Third KPI should be sum of records from the previous quarters (Q2 and Q1 for 2023), I’m facing the challenge in achieving this sum directly from the filters used in the first and second KPIs.
Hello @hellosai321, do you have a default value set on the parameter that returns the Quarter metric? Something else you could do for the 3rd KPI is create an ifelse statement that would return the date field if it meets your criteria based on the parameter value.
Since it is a KPI, date might not even be required, just the total of values from the previous dates, so you can swap dateQuarter for the field that is providing your values:
Either of those should work. If you need to convert your date field to a dateQuarter value you can always use truncDate to make that happen. If that resolves your problem, please mark my answer as a solution. Otherwise, let me know if you have any follow-up questions.
Hello @hellosai321, did my last response help guide you to your expected result? If so, please mark it as a solution. Otherwise, please let me know what follow-up questions you have on the issue.
Hello @hellosai321, are both the parameter and the field the same? If so then you should just be able to use an ifelse: previousQuarterValues = ifelse({Quarter} < ${QuarterParam}, value, NULL)
If one of them is not an integer, but rather is a date, you can use the extract() function to extract the Quarter value, set it toString(), then use parseInt() to convert it to an integer value to compare the 2. That should give you the values you are expecting!