A simple n% filter

[Anonymous]

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

[sunny_talwar · Accepted solution]

Try this:

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

[Anonymous]

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

[sunny_talwar]

Not sure I understand you question

[Anonymous]

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

[Anonymous]

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]

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