Qlik Community

Qlik NPrinting Discussions

Discussion Board for collaboration on Qlik NPrinting.

tonneofash
Contributor

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_miszkiewic
Honored Contributor III

Re: Nprinting Filter based on Aggr?

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

7 Replies
lech_miszkiewic
Honored Contributor III

Re: Nprinting Filter based on Aggr?

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

tonneofash
Contributor

Re: Nprinting Filter based on Aggr?

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_miszkiewic
Honored Contributor III

Re: Nprinting Filter based on Aggr?

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

tonneofash
Contributor

Re: Nprinting Filter based on Aggr?

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

lech_miszkiewic
Honored Contributor III

Re: Nprinting Filter based on Aggr?

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

tonneofash
Contributor

Re: Nprinting Filter based on Aggr?

Yes, that's worked!  Thanks so much

lech_miszkiewic
Honored Contributor III

Re: Nprinting Filter based on Aggr?

Awesome. Great!

Community Browser