topic Re: Calculated Variable in Set Analysis in QlikView

Calculated Variable in Set Analysis

stabben23 — Wed, 10 Oct 2012 06:53:13 GMT

Hi, I want to use my calculated variable in a set expression.

Vaiable=sum(OrderPrice*InvoicedSalesQty*RateFactor)+sum([Net cost 1]+[Net cost 3]) - sum([Total discount amount])

Expression=sum({$<Year = {$(=only(Year))}>} $(vNetSalesValue))

This will give me '-' in my calculation.

I know that this will get a sum sum sum spagetti, but how should my expression looks like instead of $(vNetSalesValue)?

if i only use = $(vNetSalesValue) in my expression everything works fine.

Re: Calculated Variable in Set Analysis

Anonymous — Wed, 10 Oct 2012 06:56:51 GMT

Hm, hard to say without looking at the document but try putting an Aggr() around your $(vNetSalesValue).

sum({$<Year={$(=only(Year))}>} Aggr($(vNetSalesValue), Dim1, Dim2, etc.))

Re: Calculated Variable in Set Analysis

stabben23 — Wed, 10 Oct 2012 07:02:17 GMT

--> Johannes,

Quick, easy and just what i need!!

Thanks

Re: Calculated Variable in Set Analysis

Wed, 10 Oct 2012 07:08:39 GMT

Hi,

Maybe this one should work. (Untested) I have created 2 variables.

vNetSalesValue = OrderPrice*InvoicedSalesQty*RateFactor

vNetSalesValue1 = [Net cost 1]+[Net cost 3]

The expression looks like this.

sum({$<Year = {$(=only(Year))}>} $(vNetSalesValue))
+sum({$<Year = {$(=only(Year))}>} $(vNetSalesValue1))
-sum({$<Year = {$(=only(Year))}>} [Total discount amount])

Regards,

Janzen

Re: Calculated Variable in Set Analysis

stabben23 — Wed, 10 Oct 2012 07:28:00 GMT

Hi Janzen,

That how i build my expression first (almost) and yes that works.

I was to fast answering Johannes because now

Expression last year=sum({$<Year = {$(=only(Year)-1)}>} aggr($(vNetSalesValue),dim))

dont behave as i want.,I will get this years value.

And how to use when i dont have any dimensions? for exampel in a gauge YTD data.

Is $#= whatever combination a way to get the calculated value?

Re: Calculated Variable in Set Analysis

Wed, 10 Oct 2012 07:35:39 GMT

Hi,

What answer works for your problem? Is it Johannes answer or my answer? $(=only(Year)-1) must give you last year if you need last year.

Regards,

Janzen

Re: Calculated Variable in Set Analysis

stabben23 — Wed, 10 Oct 2012 07:47:59 GMT

Hi,

Johannes works with

sum({$<Year = {$(=only(Year))}>} aggr($(vNetSalesValue),dim,dim)) but not for $(=only(Year)-1) vierd.

your answer will works but i try to avoid that kind of set expressions and instead use variables, if it works?!

Re: Calculated Variable in Set Analysis

stabben23 — Wed, 10 Oct 2012 08:05:52 GMT

this is

sum({$<Year = {$(=only(Year))}>} aggr($(vNetSalesValue),dim)) and

sum({$<Year = {$(=only(Year))-1}>} aggr($(vNetSalesValue),dim))

all dimenssion value are the same, Total on top is correct.

Re: Calculated Variable in Set Analysis

Wed, 10 Oct 2012 08:15:01 GMT

Hi,

It should be

sum({$<Year = {$(=only(Year)-1)}>} aggr($(vNetSalesValue),dim))

-1 should be right after the first closing parenthesis.

Regards,

Janzen

Re: Calculated Variable in Set Analysis

stabben23 — Wed, 10 Oct 2012 08:24:42 GMT

Hi,

sorry, edit the -1 in the answer, it is like this: sum({$<Year = {$(=only(Year)-1)}>} aggr($(vNetSalesValue),dim)) in the expression and dont works.