Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

liviumac
New Contributor III

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

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









1 Solution

Accepted Solutions
mdmukramali
Valued Contributor II

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

Dear ,

can you try Set analysis :

Dimension: Month ,City

Expression : Avg( {$<Client= {"=Sum(Sales)>100000"}>} [Number of orders])

if you are looking for something else share with us sample data.

Thanks,

Mukram.

3 Replies
mdmukramali
Valued Contributor II

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

Dear ,

can you try Set analysis :

Dimension: Month ,City

Expression : Avg( {$<Client= {"=Sum(Sales)>100000"}>} [Number of orders])

if you are looking for something else share with us sample data.

Thanks,

Mukram.

liviumac
New Contributor III

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

your formula works, thank you very much, this is a big breakthrough for me - being able to use calculated measures as set modifiers

it doesn't take into account sales per client per month but total sales per client, but I'm sure it can be tweaked

my idea of creating a separate table and flagging those cities with sales larger than the thresholds also works with dummy data, so the thinking was ok

it just does not work with my original data and I will try to find out why

so thank you once again, this is a great community

liviumac
New Contributor III

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

just in case anybody reads here, I have just discovered that one can in fact use Applymap to solve my original problem by constructing a compound field based on Month & City

Community Browser