Measure as a filter

[Qliklosophy]

Hi All,

I am having some trouble with creating a measure as a filter in qlik  and I have already tried to search many other related threads, but was unable to find a similar problem.

My data looks as follows:

Year	Customer	Product	Price
2000	1	1000	10
2001	1	2000	11
2002	1	1000	12
2003	1	3000	13
2000	2	1000	14
2001	2	2000	15
2002	2	1000	16
2003	2	3000	17

What I am trying to do, is make a filter so that you only see the products which have a higher average price for customer 1 compared to the maximum price of customer 2, whilst it still can be affected by year.  In the table I can make a new column with an if statement which puts a "YES" behind every product where the avg price for customer 1 is higher than the max price of customer 2 and otherwise a "NO" (whilst it is still affected by year). This column is quite usefull, but what I really want is to be able to filter this.

Anyone maybe have a solution for this?

[marcus_sommer · Accepted solution]

You may try something like this as starting point for the creation of a calculated dimension:

pick(rangesum(-(aggr(avg({< Customer -= {'Customer2'}>} Price) > max({< Customer = {'Customer2'}>} Price), Year, Customer)), 1), 'No', 'Yes')

With the real data it might be not so simple - if Price isn' just a field else an own calculation and if the comparing should happens within a different dimensionality - both would require further aggr() or maybe TOTAL on the inside respectively the outside - and if there are further fields/selections are included (probably it should work with selections and not with hard-coded customers).

If it's more than nice to have and you want to try to develop such logic - do it step by step slicing each expression part and merging them only to bigger calculations if the lower parts result in the expected values.

- Marcus