mode function glitch ?

[coloful_architect]

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]

Because clearly I have too much free time...

There you go. Significance to 14 decimal places... fun.

Rounding should solve the problem, I'm guessing.

[coloful_architect]

I was thinking if the decimal makes difference too... but fail to find the difference......

14 decimals... I gotta give you a big thumb!!!!

any round up formula I can insert into mode ? 

[Or]

I've never tried rounding in mode() but I see no reason why you couldn't use round() since mode just reads values, it doesn't care how you obtained those values. Given the significance (14 digits) I'm guessing this might be some sort of float / real number type behavior vs a standard decimal, but that's just guesswork. Rounding in script may be sufficient, and if not, you can probably round inside the mode - round(Bigformulahere,0.00001) should do it?

[coloful_architect]

ok now I stumble on adding the round into those nested set expression

____________________________________________my formula________________________________

mode (

Round(
Aggr(Sum({$<Month={"$(=Max(Month))"},[$ Sales]={'>0'}>} [$ Sales])

/Sum({$<Month={"$(=Max(Month))"},[QTY Each]={'>0'}>} [QTY Each]),[Supplier],[FK Member])),0.001)

*1.06

but the system returns this 

can you check this for me ? much appreciated 

[Or · Accepted solution]

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.

[marcus_sommer · Accepted solution]

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

[coloful_architect]

you are such a life saver!!! Your free time saves someone's day not wasted at all!!!

[coloful_architect]

ummm....so we have to be more mindful for any funky result like this or better to have a round up every single time  for all those statistics modeling ? 

how I can know this without posting this ......

really appreciate your explanation. Marcus!!!