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.
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.
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.
