Filter which shows the customers with a lower price from a specific customer

[charlie2]

Hi,

I'm trying to set up a filter directly in the front end which selects the customers which have a lower price and also purchase lower quantities compared to a specific customer within the range. The filter shall return "Yes" or "No".

So far I set up the following formula as a dimension in a table, which displays the prices per customer and article code.

=if(avg(Price) < min({<[Customer] = {'20260286'} >} Price)

AND sum(Quantities) < sum({<[Customer] = {'20260286'} >} Customer), 'Yes', 'No')

The formula doesn't have any typos. However, when I set it up, the result is not displayed but invalid is shown.

Any help would be highly appreciated

Thanks already for your time!

[pedrobergo]

Hi @charlie2 

Qlik don't provides a measuring calculation inside dimension, but enable handling texts inside measures. So, you can create this returning text formula ('Yes'/'No') as a Measure, not dimension. But, maybe you know that, measures can't be used as a filter. 😕

To figure it out, you'll need to change the measures at table:

1. Create a Button or Dropdown using the Input Variable Bundle to setting up a variable, 1 (one) if you want to filter and 0 (zero) if you don't. Let us call it as vLowerPrice
2. Use this variable at your Measure price calculation options: 

=if($(vLowerPrice) = 1, <calculate lower price>, <calculate normal price> )

[],

Pedro

[charlie2 · Accepted solution]

Hi @pedrobergo ,
 

I created it directly in back end