Skip to main content
Announcements
Defect acknowledgement with Nprinting Engine May 2022 SR2, please READ HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Nprinting Filter based on Aggr?

Hi community,

I was wondering if it is at all possible to filter out a calculated field in Nprinting?

In my case, the calculation is as follows:

=if(count(distinct([Order Unit Price]))=1, 'L', if(count(distinct([Order Unit Price])) <4, 'M', 'H' ))

This is basically giving a score for the number of different prices being offered, by product and customer (Low, medium, high).  The score is on a straight table, which is pasted into Excel in Nprinting.

I would like to filter out all of the 'L's (Lows) from Nprinting, as they are not relevant to the user.  I tried creating a variable that aggregates the expression over product and customer and then filtering out the 'L's in that variable, however the filter only selects the relevant products, not customer/products.

There is a workaround that I could use, but it would involve setting a conditional colour of white to hide rows that are 'Low'.  For the sake of performance and processing time (the Nprinting takes 16 hours normally), is there another way to do it?

Thanks,

Henry

1 Solution

Accepted Solutions
Lech_Miszkiewicz
Partner Ambassador/MVP
Partner Ambassador/MVP

Well I still think it is possible,

1st issue - you need single dimension - no problem - just concatenate SkuId&'-'&CustomerOrderId as one field and use dimension limit on it

2nd issue -  you can use second option "Only values that are greater than" and put value 1 (in place where on your screenshot is 5%) and change "relative to total" to "exact value"

what do you reckon?

cheers

Lech

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.

View solution in original post

7 Replies
Lech_Miszkiewicz
Partner Ambassador/MVP
Partner Ambassador/MVP

Can you do following:

  • create a table you are using
  • put your count(distinct([Order Unit Price])) in first expression (it can be set to "Invisible") in QlikView/
  • use Dimension Limits to values  > 1 in a QlikVIew chart
  • use this chart for NPrinting report

regards

Lech

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.
Anonymous
Not applicable
Author

Thanks for the reply Lech.

Unfortunately, this will not work for a couple of reasons.

Firstly, as there are two dimensions, I cannot apply a singular limit to both dimensions (customer and product).

Secondly, I have no option for setting a specific value to limit the dimension (is this an option in a later version of Qlik?), only the three categories in the screengrab below. 

Qlik community dimension limit.png

Thanks,

Henry

Lech_Miszkiewicz
Partner Ambassador/MVP
Partner Ambassador/MVP

Well I still think it is possible,

1st issue - you need single dimension - no problem - just concatenate SkuId&'-'&CustomerOrderId as one field and use dimension limit on it

2nd issue -  you can use second option "Only values that are greater than" and put value 1 (in place where on your screenshot is 5%) and change "relative to total" to "exact value"

what do you reckon?

cheers

Lech

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.
Anonymous
Not applicable
Author

Would I have to create SkuId&'-'&Customer ID in the load script or as a calculated dimension?

Lech_Miszkiewicz
Partner Ambassador/MVP
Partner Ambassador/MVP

Calculated dimension should still work ok - just give it a try and see how it goes.

on the other hand I would do it in script....

In Load script it would be ideal, and better for performance, but I guess such composite key may be difficult to achieve as it may be coming from different dimensional tables. Then you would have to create such key probably in Fact Table. At the same time if you decide to create such concatenated key in data model then also you can calculate your aggregate  (count(distinct([Order Unit Price]))) in script and flag records greater than 1... and than just use a flag as a filter in your report.

but your quick win is calculated dimension - I just tested it and it worked.

cheers

Lech

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.
Anonymous
Not applicable
Author

Yes, that's worked!  Thanks so much

Lech_Miszkiewicz
Partner Ambassador/MVP
Partner Ambassador/MVP

Awesome. Great!

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.