Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Showing results for 
Search instead for 
Did you mean: 

Date calculation and Set function


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


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.

1 Reply
Master III
Master III

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.