Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Highlighted
cbaqirdhds
Valued Contributor

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

Re: Set Analysis with Aggr/Rank or Min?

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

4 Replies
ahaahaaha
Honored Contributor

Re: Set Analysis with Aggr/Rank or Min?

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)

Re: Set Analysis with Aggr/Rank or Min?

Re: Set Analysis with Aggr/Rank or Min?

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

cbaqirdhds
Valued Contributor

Re: Set Analysis with Aggr/Rank or Min?

You are amazing!