topic Sum-Formular with Variable & where statement in QlikView

Sum-Formular with Variable & where statement

carolin01 — Tue, 03 Dec 2013 11:32:25 GMT

Hi,

could anybody help me to create this formular correctly:

If($(vCurrency)='EUR', sum (<FYear=($(vPreviousYear)>NettAmountEUR), If($(vCurrency)='USD', sum (<FYear=($(vPreviousYear)>NettAmountUSD), Sum(<FYear=($(vPreviousYear)>NettAmountJPY))), where OrderPhaseDesc = 'Bookings'

I would like to see the sum of the OrderPhaseDesc "Bookings" relying on the variable vCurrency either as EUR, USD or JPY for the previous year.

The variable for the previous year is: only(year(today())-1) . The variable vCurrency can just either indicate EUR, USD or JPY.

Many thanks in advance!

Carolin

Re: Sum-Formular with Variable & where statement

swuehl — Tue, 03 Dec 2013 12:02:55 GMT

Maybe like this:

=sum({<FYear = {$(vPreviousYear)}, OrderPhaseDesc = {Bookings} >} NetAmount$(vCurrency) )

Re: Sum-Formular with Variable & where statement

carolin01 — Tue, 03 Dec 2013 12:58:47 GMT

That is much shorter and very close. It does not concatenate the NettAmount with the vCurrency formular.

sum({$<FYear = {$(vPreviousYear)}, OrderPhase_Desc = {Bookings}>}NettAmount{$(vCurrency)})

Re: Sum-Formular with Variable & where statement

swuehl — Tue, 03 Dec 2013 14:10:57 GMT

I don't think you should use curly brackets with the currency dollar sign expansion.

sum({$<FYear = {$(vPreviousYear)}, OrderPhase_Desc = {Bookings}>}NettAmount$(vCurrency) )

should be enough if the vCurrency variable does not introduce e.g. leading spaces.

If you use above as expression in e.g. a straight table with no expression label, then hovering over he expression label cell, what do you see?

Re: Sum-Formular with Variable & where statement

carolin01 — Tue, 03 Dec 2013 14:35:04 GMT

It shows the following:

But the value is zero... 😉

Re: Sum-Formular with Variable & where statement

swuehl — Tue, 03 Dec 2013 14:46:15 GMT

NettAmountEUR is the correct field name, right?

So I think this is working correctly.

You still have an issue using the vPreviousYear variable, define the variable in variable overview with a leading equal sign

=only(year(today())-1)

edit: please have a look into

http://community.qlik.com/blogs/qlikviewdesignblog/2013/11/04/the-magic-of-variables

Re: Sum-Formular with Variable & where statement

carolin01 — Wed, 04 Dec 2013 09:28:12 GMT

You still have an issue using the vPreviousYear variable, define the variable in variable overview with a leading equal sign

=only(year(today())-1)"

This was the solution!!!

Thank you