10 Replies Latest reply: May 10, 2013 2:15 AM by Qnica Krumova

# Sum Formula

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,

• ###### Re: Sum Formula

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

Regards

MultiView

• ###### Re: Sum Formula

That is correct.

• ###### Re: Sum Formula

Hi,

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

Yani

• ###### Re: Sum Formula

Hi Yani!

unfortunately that did not work.

can you suggest any other tips?

Best,

• ###### Re: Sum Formula

I test it and I think it work, only not sure that the result is correct. Something that I miss is that the espression ListPricex must be before the expression Compare ListPrice. Try to do that.

Best,

Yani

• ###### Re: Sum Formula

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

• ###### Re: Sum Formula

hi Anders!

is it possible to sum on the expression value ListPricex?

i have tried

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

syntax incorrect.

• ###### Re: Sum Formula

What you want is something like this

(ListPricex -

(SUM( BaseListprice*ExchangerateEUR) / Qty)

)

/

(

SUM( BaseListprice*ExchangerateEUR) / Qty

)

• ###### Re: Sum Formula

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

• ###### Re: Sum Formula

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