Announcements
Applications are open for the 2024 Qlik Luminary Program. Apply by December 15 here.
cancel
Showing results for
Did you mean:
Contributor III

## How can I get Mode of a dimension?

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:
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:
customer_id, customer_name
1,Jon
2,Michael
3,June
];

Labels (1)
• ### General Question

1 Solution

Accepted Solutions
Former Employee

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

5 Replies
Former Employee

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.

Contributor III
Author

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)

Former Employee

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

Contributor III
Author

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.

Former Employee

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