Solved: mode function glitch ? - Qlik Community

coloful_architect · ‎2021-03-17

ok..I have a table with two dimensions:

Supplier

Member (sub member of supplier)

Three measurements:

sales

units

Price (sales/ units)

Please find the raw data tab of the attached of Qlik Sample.

I am now building a table to show supplier 's overall ACP and Mode price by member

sth like this .

Supplier	ACP	Mode
IB	$52.86	$48.08

since we want to show mode price by member, I use the mode + aggr expression

like this :

mode (
Aggr(Sum([$ Sales]),[Supplier],[Member])
/
Aggr(Sum([Units),[Supplier],[Member]))
*1.06

note: 1.06 is just a factor , does not matter here.

for some reason, for the IB supplier, it gives me the mode price of $48.08 rather than the correct one of $47.14

any thoughts why it has this error , or anything I need to fix for my mode formula ?

Or · ‎2021-03-18

It looks like you put the parameter for your round() outside of the round() structure, I think? Qlik is trying to apply it to the mode() so it's probably in the wrong place.

View solution in original post

marcus_sommer · ‎2021-03-18

Agree with Or the rounding belonged inside the aggr() like:

mode(aggr(round(sum([$ Sales]) / sum([Units]), 0.01),[Supplier],[Member])) * 1.06

Beside this your challenge is related to the in Qlik used number-system which is a binary-system which doesn't have for the most numbers an exact decimal-value. It's not bad in itself because it increased the processing-speed because it didn't need an additionally abstraction-layer to calculate anything. A disadvantage is that you could not simply compare or match numbers against each other, unless you applies an appropriate rounding.

- Marcus

View solution in original post

Or · ‎2021-03-17

I can't set up a test app at the moment, but perhaps something along the lines of:

mode (
Aggr(Sum([$ Sales])/Sum([Units),[Supplier],[Member]))
*1.06

You should be able to place both Sum()s in the same aggr(), if I'm not mistaken. Not sure if this would cover the desired use case here.

marcus_sommer · ‎2021-03-17

You may try it in this way:

mode(Aggr(Sum([$ Sales]) / Sum([Units]),[Supplier],[Member])) * 1.06

- Marcus

coloful_architect · ‎2021-03-17

No ...I tried ....still shows the 48.08 as the mode price rather than 47.14

coloful_architect · ‎2021-03-17

No ...I tried ....still shows the 48.08 as the mode price rather than 47.14

Or · ‎2021-03-17

The mode *is* 48.08 rather than 47.14, unless I'm missing something? That's what I get for Mode(Price) after selecting IB, anyway.

coloful_architect · ‎2021-03-17

if you check the excel file with countif excel formula, the mode is $47.14 with the most count of 14

$48.08 would be the second most value with the count of 11.

Or · ‎2021-03-17

Not quite: it appears your data has two different 47.14 values. One appears 10 times, and one appears 4 times. I'm not sure what causes them to be different, but that's what Qlik is seeing, so 11 checks out as the mode.

coloful_architect · ‎2021-03-17

ah...interesting....so is that possible to group those $47.14 together by sort of setting to make it as the mode?

Or · ‎2021-03-17

I think you'll have to figure out why they're coming out as two different values in the first place.. I've got no idea, myself. The same thing happens with other values, e.g. 47.14085, which only has two rows so it might be easier to check.