topic Re: How can I get Mode of a dimension? in App Development

How can I get Mode of a dimension?

parviz_asoodehfard — Fri, 04 Mar 2022 14:51:48 GMT

Getting the Mode of the dimension key is easy but how we can get the mode of a dimension?

To make answering easier, I put an example of data here

The Mode(customer_id) works fine. However, Here I'm looking for the Mode of customer_name based on the fact table but it doesn't work by mode(customer_name) as you can see. So I'm looking for a formula that generates the "My expectation" column.

what would be an expression for the "My expectation" column?

--------------------------------------

fact_purchase:
Load * Inline [
fact_id,customer_id, purchased_item
1,1,1
2,2,1
3,3,1
4,3,1
5,1,2
6,2,2
7,2,2
8,1,3
];

dim_customer:
Load * Inline [
customer_id, customer_name
1,Jon
2,Michael
3,June
];

Re: How can I get Mode of a dimension?

hic — Fri, 04 Mar 2022 15:39:54 GMT

No, the Qlik engine is doing the right thing.

First of all, your dimension is "purchased item", and since "customer_id" exists in the same table as "purchased item", this is where the Mode("customer_id") will be evaluated:

Note that Mode() corresponds perfectly to the multiplicities shown by Concat().

However, with "customer_name" it is a different thing: It is in the "dim_customer" table, so now the evaluation of Mode() is made there:

Note that all three names exists exactly once, so for "purchased_item" = 1 or two, it is not possible to say which of the names is more common.

Re: How can I get Mode of a dimension?

parviz_asoodehfard — Fri, 04 Mar 2022 16:48:49 GMT

I agree that Qlik works fine. But I have a valid question and hope to resolve it.

So, my question is how Qlik expression can help me to find an answer to this question? (maybe using something like nested aggregation)

Re: How can I get Mode of a dimension?

hic — Fri, 04 Mar 2022 17:26:48 GMT

Mode(Aggr(Only(customer_name),fact_id))

Re: How can I get Mode of a dimension?

parviz_asoodehfard — Fri, 04 Mar 2022 18:34:13 GMT

Great.

I brought fact_id to make it easier talking about rows. My table doesn't have fact_id. Is there any solution without fact_id?

By the way Thanks, Sir. I've just learned "Only" function.

Re: How can I get Mode of a dimension?

hic — Fri, 04 Mar 2022 18:38:55 GMT

You need some field to define what you mean by a "row". fact_id is perfect for this.

Add

RecNo() as fact_id

to your fact table in the script.