topic Re: Complicated AGGR in QlikView

Complicated AGGR

cbaqir — Tue, 06 Jun 2017 15:24:35 GMT

I only want to include rows in which max(DEFECT_STATUS_START_DATE)-min(DEFECT_STATUS_START_DATE)>1 for both the calculation of my Avg # days and the count of DEFECT_ID.

The Avg # days is using a denominator of the total count of DEFECT_IDs instead of just those in which max(DEFECT_STATUS_START_DATE)-min(DEFECT_STATUS_START_DATE)>1 . As you can see, if 10-13 is selected for the month and the Statuses selected are New and Approved, the Avg # days shows 8 but (27+12)/2 which should be 19.5. It's including those DEFECT_IDs where max(DEFECT_STATUS_START_DATE)-min(DEFECT_STATUS_START_DATE)<1.

How can I fix this?

if(max(DEFECT_STATUS_START_DATE)-min(DEFECT_STATUS_START_DATE)>1,$(vAvgDays))

vAvgDays = SUM( Aggr( Max(DEFECT_STATUS_START_DATE)- Min(DEFECT_STATUS_START_DATE) ,DEFECT_ID) )

/Count({$<DEFECT_ID = {"=Count(DEFECT_STATUS_MAP) = GetSelectedCount(DEFECT_STATUS_MAP)"}>}DISTINCT DEFECT_ID)

Same question on the # of defects. It shows 1 when there should be 2 in this case.

Thanks all!

scottford

Re: Complicated AGGR

felipedl — Tue, 06 Jun 2017 16:26:16 GMT

Hello Cassandra,

Correct me if I understood it wrong,

but if you need the value to be 39, just let your vAvgDays variable be:

=SUM(

Aggr(

Max(DEFECT_STATUS_START_DATE)- Min(DEFECT_STATUS_START_DATE)

,DEFECT_ID)

)

Below is the following:

1) Your number, with the function you defined on the vAvgDays

2) Same expression withouth the denominator (as I pasted above).

And the table has both of them as expressions (just added the 2) expression to the table you aldready had).

Hope it helps,

Felipe.

Re: Complicated AGGR

cbaqir — Tue, 06 Jun 2017 16:27:00 GMT

I want the average so 39/2.

Re: Complicated AGGR

cbaqir — Tue, 06 Jun 2017 16:33:55 GMT

I'm looking for the right count, Felip, not the sum.

Re: Complicated AGGR

cbaqir — Tue, 06 Jun 2017 16:46:04 GMT

stalwar1‌

Re: Complicated AGGR

sunny_talwar — Tue, 06 Jun 2017 16:58:15 GMT

This

Avg(Aggr(if(max(DEFECT_STATUS_START_DATE)-min(DEFECT_STATUS_START_DATE)>1,$(vAvgDays)), MONTH_1, DEFECT_ID))

Re: Complicated AGGR

cbaqir — Tue, 06 Jun 2017 17:04:18 GMT

If you select Oct 2013, That doesn't seem to give me any value for Avg # days and also what about the Count formula?

if(max(DEFECT_STATUS_START_DATE)-min(DEFECT_STATUS_START_DATE)>1,1,0)

Re: Complicated AGGR

sunny_talwar — Tue, 06 Jun 2017 17:11:45 GMT

Where on the sheet do I select that value?

Re: Complicated AGGR

cbaqir — Tue, 06 Jun 2017 17:22:36 GMT

It looks like it's already showing only October in your screenshot but you can select it in the straight table.

Re: Complicated AGGR

sunny_talwar — Tue, 06 Jun 2017 17:23:57 GMT

So if it is already selected, I see 19 in the chart next to it... isn't that what you want?

Re: Complicated AGGR

cbaqir — Tue, 06 Jun 2017 17:36:01 GMT

Yes, and for the count to match the table and show 2.

Re: Complicated AGGR

sunny_talwar — Tue, 06 Jun 2017 17:38:12 GMT

Try this

Sum(Aggr(If(Max(DEFECT_STATUS_START_DATE)-Min(DEFECT_STATUS_START_DATE)>1,1,0), DEFECT_ID, MONTH_1))

Re: Complicated AGGR

cbaqir — Tue, 06 Jun 2017 19:37:48 GMT

Thanks as always Sunny!

How would I add the syntax to say specifically to return a count of DEFECT_STATUS_MAP = Closed DEFECT_ID to this?

Sum(Aggr(If(Max(DEFECT_STATUS_START_DATE)-Min(DEFECT_STATUS_START_DATE)>1,1,0), DEFECT_ID,FISCAL_YEAR))

Re: Complicated AGGR

sunny_talwar — Tue, 06 Jun 2017 19:42:32 GMT

May be this

Sum({<DEFECT_STATUS_MAP = {'Closed'}>}Aggr(If(Max(DEFECT_STATUS_START_DATE)-Min(DEFECT_STATUS_START_DATE)>1,1,0), DEFECT_ID,FISCAL_YEAR))

or this

Sum({<DEFECT_ID = {"=Count(DISTINCT {<DEFECT_STATUS_MAP = {'Closed'}>} DEFECT_ID) > 0"}>}Aggr(If(Max(DEFECT_STATUS_START_DATE)-Min(DEFECT_STATUS_START_DATE)>1,1,0), DEFECT_ID,FISCAL_YEAR))