topic Re: Set Analysis with Aggr/Rank or Min? in QlikView

Set Analysis with Aggr/Rank or Min?

cbaqir — Wed, 26 Jul 2017 22:20:38 GMT

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

Re: Set Analysis with Aggr/Rank or Min?

ahaahaaha — Thu, 27 Jul 2017 08:24:13 GMT

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?

Miguel_Angel_Baeyens — Thu, 27 Jul 2017 09:30:59 GMT

See

Count items with max date‌

and Finding matching dimension for min value‌

and Find out min and max Dates and show a value only from this row

Re: Set Analysis with Aggr/Rank or Min?

sunny_talwar — Thu, 27 Jul 2017 11:09:41 GMT

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

Re: Set Analysis with Aggr/Rank or Min?

cbaqir — Thu, 27 Jul 2017 14:16:03 GMT

You are amazing!