Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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) )
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) )
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
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) )
Added the Month/Year ignore set expression on outer sum function and works fine.
There is no documentation on this.