Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Ignoring specific dimension in AGGR function

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.

1 Solution

Accepted Solutions
rubenmarin

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

View solution in original post

4 Replies
rubenmarin

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

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

Not applicable
Author

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

There is no documentation on this.