Count of Mode of 3 out of 4 dimensions

shane_spencer — Wed, 26 Feb 2025 15:48:30 GMT

I have 4 Dimensions in a table [Lock Local Period], [TDW Legal Entity Code], [TDW Amz Reg Jurisdiction] and [TDW Amz Reg] (the field [Full Legal Entity Name] can be ignore as it's the long name of [TDW Legal Entity Code]). See screenshot.

I am trying to create some Measures about [TDW Amz Reg] based on the following 3 dimensions [Lock Local Period], [TDW Legal Entity Code], [TDW Amz Reg Jurisdiction]. For example the (most frequent) Mode [TDW Amz Reg] for [Lock Local Period], [TDW Legal Entity Code], [TDW Amz Reg Jurisdiction] and the Count of the Distinct [TDW Amz Reg] by [Lock Local Period], [TDW Legal Entity Code], [TDW Amz Reg Jurisdiction]. i.e.

=aggr( Mode([TDW Amz Reg]), [Lock Local Period], [TDW Legal Entity Code], [TDW Amz Reg Jurisdiction])

Count(Distinct TOTAL <[Lock Local Period], [TDW Legal Entity Code], [TDW Amz Reg Jurisdiction]> [TDW Amz Reg])

So far so good. Now I want to count of how frequently the Mode [TDW Amz Reg] per [Lock Local Period], [TDW Legal Entity Code], [TDW Amz Reg Jurisdiction] appears. I'm struggling because I don't think Aggr will work with having the [TDW Amz Reg] as a column.

topic Count of Mode of 3 out of 4 dimensions in QlikView

Count of Mode of 3 out of 4 dimensions