Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
HI,
I have a customer feedback table with 4 columns
FB_ID is unique
FB_ID | FB_Date | FB_Acknowledge_Date | FB_Resolve_Date |
1 | 12/05/2021 | 14/05/2021 | 28/07/2021 |
2 |
12/07/2021 | 12/07/2021 |
Now we have to calculate the count of long Pending FB_ID , and both the condition should be -
1> FB_Resolve_Date = null , as it is not resolved yet.
and 2>max(FB_Date) - FB_Date >90 days. As the data source is excel and we have data till October 21' only.
Hence both the conditions should be applied to calculate the Long Pending feedback.
Please help me with the set expression.
Need to create a KPI.
I assume that by max(FB_Date) you mean across the entire dataset, since if you used this in the context of an FB_ID there would only ever be one FB_Date.
Based on that assumption, I would suggest you pull the max value into a variable (as detailed, for example, here: https://community.qlik.com/t5/New-to-Qlik-Sense/Getting-Max-Value-of-a-Field/td-p/1035197 )
You could then write a fairly simple formula along the lines of:
count(if(isnull(FB_Resolve_Date) AND vMaxDate - FB_Date > 90,FB_ID))
That would prevent the need to nest aggregations with aggr() inside your KPI.