Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
sunny_talwar

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
sunny_talwar

Try this:

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


Capture.PNG

Anonymous
Not applicable
Author

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

sunny_talwar

Not sure I understand you question

Anonymous
Not applicable
Author

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.

sunny_talwar

Try using it as a measure instead of dimension

Capture.PNG

Anonymous
Not applicable
Author

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!

sunny_talwar

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.

Anonymous
Not applicable
Author

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