topic Re: Sum Formula in QlikView

Sum Formula

Thu, 09 May 2013 14:11:34 GMT

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

Re: Sum Formula

Anonymous — Thu, 09 May 2013 14:37:16 GMT

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

Regards

MultiView

Re: Sum Formula

Thu, 09 May 2013 14:39:57 GMT

That is correct.

Re: Sum Formula

Thu, 09 May 2013 14:44:42 GMT

Hi,

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

Yani

Re: Sum Formula

calvindk — Thu, 09 May 2013 14:44:51 GMT

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

Thu, 09 May 2013 14:50:33 GMT

Hi Yani!

thank you for your reply

unfortunately that did not work.

can you suggest any other tips?

Best,

Brad

Re: Sum Formula

Thu, 09 May 2013 14:59:50 GMT

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.

Re: Sum Formula

calvindk — Thu, 09 May 2013 15:01:08 GMT

What you want is something like this

(ListPricex -

(SUM( BaseListprice*ExchangerateEUR) / Qty)

)

(

SUM( BaseListprice*ExchangerateEUR) / Qty

)

Re: Sum Formula

calvindk — Thu, 09 May 2013 15:03:58 GMT

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

Anonymous — Thu, 09 May 2013 15:27:44 GMT

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

Re: Sum Formula

Fri, 10 May 2013 06:15:03 GMT

Hi Brad!

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