Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
kushagra_jain
New Contributor

Aggr() function with If() condition

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)
qlik_eg.PNG

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

1 Solution

Accepted Solutions

Re: Aggr() function with If() condition

May be this

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

Re: Aggr() function with If() condition

May be this

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

Re: Aggr() function with If() condition

Thanks, this works 🙂