Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
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
Highlighted

Re: Ignoring specific dimension in AGGR function

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
Highlighted

Re: Ignoring specific dimension in AGGR function

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

Highlighted

Re: Ignoring specific dimension in AGGR function

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

Highlighted

Re: Ignoring specific dimension in AGGR function

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

Highlighted
Not applicable

Re: Ignoring specific dimension in AGGR function

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

There is no documentation on this.