Sum Formula

[#1]

Hi I am having a nightmare with a sum formula;

i wish to show the sum of all article variation (ListPrice/BasePrice) over time dimension.

i have attached an example qvw whereby i can get all variations on the item level to work, but not over time in the chart.

Help is greatly appreciated.

Best regards,

Bradley

[Anonymous]

Which column you have issue with? Is it Compare ListPrice SEK/BasePrice SEK?

Regards

MultiView

[#3]

That is correct.

[#4]

Hi,

you must have aggregate function. Try this "=(ListPricex - Sum(BaseListprice * ExchangerateEUR)) / Sum(BaseListprice * ExchangerateEUR)".

Yani

[calvindk]

You have made a very common mistake. The problem is that the BaseListPrice is not unique for the dimension. You need a way to pick which baselistprice to use for a given product

This can be handled either by summing (which is probably what you want)

or any other aggregate function

[#6]

Hi Yani!

thank you for your reply

unfortunately that did not work.

can you suggest any other tips?

Best,

Brad

[#7]

hi Anders!

Thanks for the advise:

is it possible to sum on the expression value ListPricex?

i have tried

(sum(ListPricex) - Sum(BaseListprice * ExchangerateEUR)) / Sum(BaseListprice * ExchangerateEUR).

syntax incorrect.

[calvindk]

What you want is something like this

(ListPricex -

(SUM( BaseListprice*ExchangerateEUR) / Qty)

)

/

(

SUM( BaseListprice*ExchangerateEUR) / Qty

)

[calvindk]

bracoy01 wrote:

is it possible to sum on the expression value ListPricex?

i have tried

(sum(ListPricex) - Sum(BaseListprice * ExchangerateEUR)) / Sum(BaseListprice * ExchangerateEUR).

syntax incorrect.

While you can correct the syntax, you are already using a sum. Qlikview does not allow sum of sums unless you aggr in between

[Anonymous]

The issue in short is that BaseListprice and ExchangerateEUR  fields are not aggregated.

1 - Add a new expressions with the label : sBaseListPrice and definition sum(BaseListprice)

2 - Add a new expressions with the label :  sExchangerateEUR and definition avg(ExchangerateEUR)

3 - Change your Compare ListPrice..definition to (ListPricex - (sBaseListprice * sExchangerateEUR)) /(sBaseListprice * sExchangerateEUR)

or alternatively

(ListPricex - (sum(BaseListprice) * avg(ExchangerateEUR))) /(sum(BaseListprice) * avg(ExchangerateEUR))

Regards

MultiView