How can I get Mode of a dimension?

[parviz_asoodehfard]

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
];

[hic]

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.

[parviz_asoodehfard]

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)

[hic · Accepted solution]

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

[parviz_asoodehfard]

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.

[hic]

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.