Date calculation and Set function

[gaurab]

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.

[Or]

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.