topic Re: Ignoring specific dimension in AGGR function in QlikView

Ignoring specific dimension in AGGR function

Tue, 09 Jun 2015 14:32:28 GMT

Hello All, I have a requirement to show the Accuracy% (Fact) value for Each Month/Year (Dim) irrespective of user made any selections on Month/Year.

I have a data like: Metric, Month/Year , Parent_Supplier_ID , Supplier_ID , Item_ID , Document_ID , Actual_Lead_Time, 855_Lead_Time

Blue -- Dimensions Red -- Facts

User can made selection only on Month/Year , Parent_Supplier_ID , Supplier_ID

We need to create a Line chart that shows the Accuracy% of Month/Year for either Parent_Supplier_ID or Supplier_ID means user first select Month/Year AND (Parent_Supplier_ID or Supplier_ID)

Accuracy% Definition : { Count of ItemID onli if ItemID avg Actual_Lead_Time - ItemId avg 855_Lead_Time range of -2 to 2 } / {Count of Distinct ItemID } per Month/Year

avg {Actual/855}Lead Time = Sum( {Actual/855}Lead_Time ) / Count( Docuement_ID)

Denominator : Count({<[Month/Year]=, MetricName = {'855Accuracy'}>} DISTINCT ItemID )

Numerator : Sum(IF( Aggr( fabs(

( sum({1<[Month/Year]= ,[MetricName]={'855Accuracy'}>} Actual_Lead_Time ) / Count({1<[Month/Year]= ,[MetricName]={'855Accuracy'}>} Document_ID) ) -

( sum({1<[Month/Year]= ,[MetricName]={'855Accuracy'}>} [855_Lead_Time] ) / Count({1<[Month/Year]= ,[MetricName]={'855Accuracy'}>} Document_ID) ) ) , [Month/Year],Supplier_ID, ItemID ) <= 2 , 1, 0) )

The Numerator expression working fine I made selections on the Parent_Supplier_ID or Supplier_ID but I made Month/Year the other Month/Year values showing 0.

Whats wrong the above expression ? How to Ignore the Month/Year selections with in AGGR function ?

Please find the sample qvw for reference.

Re: Ignoring specific dimension in AGGR function

rubenmarin — Tue, 09 Jun 2015 14:36:25 GMT

Hi, if you want ignore some of the selections you need to put them in the function enclosing the Aggr(), and also in the functions inside Aggr() as you're doing:

Numerator : Sum({<[Month/Year]=>} IF( Aggr( fabs(

( sum({1<[Month/Year]= ,[MetricName]={'855Accuracy'}>} Actual_Lead_Time ) / Count({1<[Month/Year]= ,[MetricName]={'855Accuracy'}>} Document_ID) ) -

( sum({1<[Month/Year]= ,[MetricName]={'855Accuracy'}>} [855_Lead_Time] ) / Count({1<[Month/Year]= ,[MetricName]={'855Accuracy'}>} Document_ID) ) ) , [Month/Year],Supplier_ID, ItemID ) <= 2 , 1, 0) )

Re: Ignoring specific dimension in AGGR function

alexandros17 — Tue, 09 Jun 2015 14:36:30 GMT

To ignore dimension in Aggr:

Example:

Aggr(Sum(value), dim1, dim2 <dim3, dim4>) where dim3 and dim4 are the dimension you need to be excludeed

hope this helps

Re: Ignoring specific dimension in AGGR function

sunny_talwar — Tue, 09 Jun 2015 14:37:44 GMT

May be this:

Sum({<[Month/Year]>}IF( Aggr( fabs(

( sum({1<[Month/Year]= ,[MetricName]={'855Accuracy'}>} Actual_Lead_Time ) / Count({1<[Month/Year]= ,[MetricName]={'855Accuracy'}>} Document_ID) ) -

Re: Ignoring specific dimension in AGGR function

Tue, 09 Jun 2015 15:16:04 GMT

Added the Month/Year ignore set expression on outer sum function and works fine.

There is no documentation on this.