Aggr() function with If() condition

[kushagra_jain]

Hello,

I am new to Qlik sense and working to get the no of clients who were active for 1 year. 2 year or so.

My dataset has these two fields EmailAddress and DateofInquiry.

Now in the chart table I took these three as my dimension:
 
EmailAddress, 

aggr(Min(date([DateOfInquiry.autoCalendar.Date],'YYYY')), EmailAddress)

aggr(max(date([DateOfInquiry.autoCalendar.Date],'YYYY')), EmailAddress)

and I am able to see like this (showing only the top 9 rows)

Now I want to know no of distinct clients whose placed last order in 2019 and their first order came in 2017.

I tried the below expression but didn't work

count(distinct if( aggr(min(date([DateOfInquiry.autoCalendar.Date],'YYYY')), EmailAddress) = '2017' and aggr(max(date([DateOfInquiry.autoCalendar.Date],'YYYY')), EmailAddress) = '2019' , EmailAddress))

Please let me know the possible solution.

@sunny_talwar please help with this

[sunny_talwar · Accepted solution]

May be this

Count(DISTINCT {<
EmailAddress = {"=Min(Year([DateOfInquiry.autoCalendar.Date])) = 2017 and Max(Year([DateOfInquiry.autoCalendar.Date])) = 2019"}
>} EmailAddress)

[kushagra_jain]

Thanks, this works 🙂