3 Replies Latest reply: Sep 14, 2017 10:23 AM by Liv ma RSS

    Please help with table associations (I don't understand what's going on)

    Liv ma

      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