Qlik Community

New to Qlik Sense

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

Announcements
QLIKWORLD LIVE! MAY 16 - 19TH, EARLY BIRD DISCOUNTS! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
gaurab
Contributor
Contributor

Date calculation and Set function

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.

1 Reply
Or
Master II
Master II

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.