Master Item Measure Calculation

[markdg]

Hello QLIK Brain Trust, 

Hoping someone can help out.

I created  a master measure
[Bottle Deposit]   which is simply sku.bottle_deposit

Works as expected

When I make a second master measure
[Bottle Deposit Ttl]  which is [Bottle Deposit] * quantity

I get the correct value BUT, in the table, the totals in the top of the column are blank.

So, when I change the expression to sum(sku.bottle_deposit * quantity)

For one of 120 records, I get (0.1 * 2) = 0.4

Does anyone know why this is happening?

Further, I then tried to use this expression but get an error.

sum([Bottle Deposit] * quantity)

Does anyone have a link to the syntax structure for the master measures AND, know why on a random record, the math is wrong?

[rwunderlich]

This is about the differences between Dimensions --which are unique values -- and aggregation in Measures -- which will aggregate all values.

I believe you have two rows of data for the dimensions (Qty =2, Item Price = 75,  sku.bottle_deposit =0.10).   So the correct result for "sum(sku.bottle_deposit * quantity)" would be 0.40. (Quantity 2 * 2). 

Why do you have two rows? Could it be that you have two different skus with those same attributes?  In which case you should add sku as a Dimension in your chart. 

Why no total for {Bottle Deposit]?  Because you did not use an aggregation function in your measure, a total cannot be computed. See this blog for more: https://community.qlik.com/t5/Qlik-Design-Blog/Use-Aggregation-Functions/ba-p/1475833

You cannot do:

sum([Bottle Deposit] * quantity)

because Measures are not allowed in aggregation functions. You must use fields in aggregations.  https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/Expressions/references-f...

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[markdg]

Hi Rob,

thanks for getting back to me so quickly.

Two columns... one is the base cost/pricing per unit and the other one is Extended (base x quantity)  In this case $0.10 per bottle x 2 bottles.

What I don't understand is that for the other items, it works fine.  

I read through your blogs and can't figure out why the following happens.

for Bottle Deposit EXT
sum(sku.bottle_deposit * quantity)

Total is $0.40  (Wrong should be $0.20) But, I get the total in the top of the column. (Should be $72.80)

Whereas  in this example where I use the expression

sku.bottle_deposit * quantity

It provides the correct value of $0.20 but no column total.

I'm missing something really small but very significant.  Thoughts?

Thanks

Mark

[anthonyj]

Hi @markdg ,

It sounds like @rwunderlich  is correct with the duplication in the rows that's being hidden by the table. The fact that it shows as $0.40 when used as a measure and $0.20 as a dimension supports this as a dimension will only show the value once but a measure will aggregate to the column.

To assist in problem solving can I suggest adding a unique key value column to the table which would expand out the rows. If there's no unique key in the data already, add one in the load script using the RECNO() as _UniqueKey.

Regards

Anthony

[rwunderlich]

If you still have questions after trying @anthonyj suggestion, could you please post a screenshot of your data model?

-Rob