Ignoring specific dimension in AGGR function

[#1]

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.

[rubenmarin · Accepted solution]

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) )

[alexandros17]

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

[sunny_talwar]

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) ) -

( 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) )

[#5]

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

There is no documentation on this.