Hello, I have a situation which I do not understand and after thinking about it for some time I decided to ask for your help
I have a data structure like this:
Month, Client, City, Sales, Number of orders
I want to calculate some metric based on the field Number of orders (such as average orders per month) but only for those clients in those cities that have total sales above eg 1 milion dollars per month
So I created a different table, with Month, City, Sum(Sales) and I flagged those cities with total sales above 1 milion
So now I have 2 tables
Table 1 (original table): Month, Client, City, Sales, Number of orders
Table 2: Month, City, [Flag for sales > 1 mio]
when I load these 2 tables, Qlik associates them through a synthetic key consisting of the fields Month+City (as expected)
now the puzzling thing is that when I put a filter on the field "Flag", Qlik shows NO DATA
why does this happen? I am completely lost. (I have checked: the months are exactly the same, the cities are exactly the same)
I cannot use ApplyMap because a city does not necessarily have Sales above 1 million each month, only in certain months, so the mapping table would have 3 columns and this does not work
is there another way to do this type of calculation?
In fact, it is a set modifier based on a calculated measure, but I do not know how to do it or if it's possible.
thank you
LM
