Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
cbaqir
Specialist II
Specialist II

Set Analysis with Aggr/Rank or Min?

I am trying to get a count of distinct defects created within the last 6 months.

I have a requirement to only account for the earliest DEFECT_STATUS_START_DATE since the defect could go through the New DEFECT_STATUS more than once. How do I account for this? Do I use min or Aggr/Rank somehow?

Count({$<DEFECT_STATUS_START_DATE={">=$(=Date(addmonths(monthend(today()),-6),'MM/DD/YYYY'))<=$(=Date(addmonths(monthend(today()),0),'MM/DD/YYYY'))"},DEFECT_STATUS={'New'}>} distinct DEFECT_ID)

TIA!

stalwar1

1 Solution

Accepted Solutions
sunny_talwar

Try this

='Tickets Created (Expecting 736): ' &

Count({$<DEFECT_ID = {"=Min({<DEFECT_STATUS={'New'}>}DEFECT_STATUS_START_DATE) >= AddMonths(Today(),-6) and Min({<DEFECT_STATUS={'New'}>}DEFECT_STATUS_START_DATE) <= AddMonths(Today(), 0)"}, DEFECT_STATUS={'New'}>} DISTINCT DEFECT_ID)

Gives me 735 because the result is based on Today's date of 7/27/2017... but if you use this

='Tickets Created (Expecting 736): ' &

Count({$<DEFECT_ID = {"=Min({<DEFECT_STATUS={'New'}>}DEFECT_STATUS_START_DATE) >= AddMonths(Today()-1,-6) and Min({<DEFECT_STATUS={'New'}>}DEFECT_STATUS_START_DATE) <= AddMonths(Today()-1, 0)"}, DEFECT_STATUS={'New'}>} DISTINCT DEFECT_ID)

which is 7/26/2017, it gives 736

View solution in original post

4 Replies
ahaahaaha
Partner - Master
Partner - Master

Hi,

May be like this

=Count({$<DEFECT_STATUS_START_DATE={">=$(=Date(addmonths(monthend(today()),-6),'MM/DD/YYYY'))"}*{"<=$(=Date(addmonths(monthend(today()),0),'MM/DD/YYYY'))"}*

{"=Aggr(Min(DEFECT_STATUS_START_DATE), DEFECT_ID)"}>} distinct DEFECT_ID)

sunny_talwar

Try this

='Tickets Created (Expecting 736): ' &

Count({$<DEFECT_ID = {"=Min({<DEFECT_STATUS={'New'}>}DEFECT_STATUS_START_DATE) >= AddMonths(Today(),-6) and Min({<DEFECT_STATUS={'New'}>}DEFECT_STATUS_START_DATE) <= AddMonths(Today(), 0)"}, DEFECT_STATUS={'New'}>} DISTINCT DEFECT_ID)

Gives me 735 because the result is based on Today's date of 7/27/2017... but if you use this

='Tickets Created (Expecting 736): ' &

Count({$<DEFECT_ID = {"=Min({<DEFECT_STATUS={'New'}>}DEFECT_STATUS_START_DATE) >= AddMonths(Today()-1,-6) and Min({<DEFECT_STATUS={'New'}>}DEFECT_STATUS_START_DATE) <= AddMonths(Today()-1, 0)"}, DEFECT_STATUS={'New'}>} DISTINCT DEFECT_ID)

which is 7/26/2017, it gives 736

cbaqir
Specialist II
Specialist II
Author

You are amazing!