Sum-Formular with Variable & where statement

[carolin01]

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

[swuehl]

Maybe like this:

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

[carolin01]

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)})

[swuehl]

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?

[carolin01]

It shows the following:

But the value is zero... 😉

[swuehl · Accepted solution]

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

[carolin01]

"

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