Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Highlighted
giulioandrea
New Contributor III

A simple n% filter

Hi all,

I am becoming crazy I cannot find the right way for create a filter for "top" Customer. I just want to separate the VIP customer from the other one.

I need just a dimension to put in my filter pannel in order to avoid that my VIP customers can distort my analysis.

I am trying with the following expression, but doesn't work:

if(aggr(sum([sales price]),[product name],[customer id]) / aggr(sum([sales price]),[product name])>0.1 ,'VIP','Normal')

ideally the following should be the right solution, but Qlik show me an error message when I try to create this field:

if(aggr(sum([sales price]),[product name],[customer id]) / sum( total <[product name]> [sales price]) >0.1 ,'VIP','Normal')

attached a test application.

Any help would be really appreciated.

N.B. I would like to find a solution directly in the front end without touching the script. I need only a dimension as my final output.

Thanks, Giulio

1 Solution

Accepted Solutions

Re: A simple n% filter

Try this:

Aggr(If(sum([sales price])/Sum(TOTAL <[product name]> [sales price]) > 0.1, 'VIP', 'Normal'), [product name], [customer id])


Capture.PNG

View solution in original post

8 Replies

Re: A simple n% filter

Try this:

Aggr(If(sum([sales price])/Sum(TOTAL <[product name]> [sales price]) > 0.1, 'VIP', 'Normal'), [product name], [customer id])


Capture.PNG

View solution in original post

giulioandrea
New Contributor III

Re: A simple n% filter

Hi Sunny Thank you for your reply. The solution seems works. Now I have added a new customer that has a low budget and that should be flagged as "Normal", the solution that you have provided me works fine because if I select VIP the new customer ID=11 is correctly avoided by the selection.

But one more thing, why when I added the new flag at my table the flag seems that is not working well for each row? Appears 'Normal' also near to customer that should have the 'VIP' flag. There is a logic on which I cannot use the same field also in one table?

Thank you in advance

Cattura.PNG

Re: A simple n% filter

Not sure I understand you question

giulioandrea
New Contributor III

Re: A simple n% filter

if you open the attached file in my previous answer you can see that the dimension that you suggest me doesn't do the job infact some row are flagged Normal instead of VIP even if the percentage is more than 20 %. although when I use the filter seems works with this little amount of data.

Re: A simple n% filter

Try using it as a measure instead of dimension

Capture.PNG

giulioandrea
New Contributor III

Re: A simple n% filter

Ok thank you very much, but why this happen? is there a technical reason for this?

P.S. thank very much Sunny this always the BEST!

Re: A simple n% filter

By adding it as a dimension, you changed from 2 to 3 dimensions and your expressions were now aggregated at three dimension. What you really needed was to add it as an expression so that the percentage expression was calculated correctly. VIP and Normal flags were right, but the percentage calculation changed because of an added dimension.

giulioandrea
New Contributor III

Re: A simple n% filter

This is a very valuable answer for me. Thanks again.