Sum & If Formula for pivot table

[carolin01]

Hi,

I would like to use the following formula but it´s not working:

=if(FPeriodDesc = FPDescToday,(sum({$<OrderPhase_Desc = {'Revenue'}>}NettAmount$(vCurrency))+(sum({$<OrderPhase_Desc = {'Backlog'}>}NettAmount$(vCurrency)))),0)

Could anybody help me?

Best regards

Carolin

[mato32188]

Hi Carolin,

what does $(vCurrency) in your expression mean? or it is just about missing multiplier (*) between NettAmount * $(vCurrency)?

Martin

[Anonymous]

Caroline,

The sum expression seems to be ok. Maybe try with only function in if statement:

= if(only(FPeriodDesc) = only(FPDescToday), sum(....

Marc.

[carolin01]

$(vCurrency) is just a variable to be able to calculate in EUR or USD depending on what the user wants to see. There is a NettAmount field for each of the currencies.

[carolin01]

Did not help unfortunately. The expression is shown as ok in Qlik View until I use the "+" sign.

[Anonymous]

Carolin,

A sample data will help us.

Marc.

[#7]

Hi,

What does exactly $(vCurrency) will do...is it will convert into EUR/USD or add EUR/USD representation?

Try with out without $(vCurrency)..

=if(FPeriodDesc = FPDescToday,(sum({$<OrderPhase_Desc = {'Revenue'}>}NettAmount)+(sum({$<OrderPhase_Desc = {'Backlog'}>}NettAmount))),0)

[giacomom]

Hi Carolin,

does the $(vCurrency) variable contain a string ('EUR', 'USD')? And are the fields named NetAmountEUR and NetAmountUSD? Because it looks like you are accessing the fields by concatenating part of the field name with the variable but it doesn't work even though the expression editor says ok.

In that case you should split the two cases with a nested if like this:

=if(FPeriodDesc = FPDescToday, if($(vCurrency)='EUR', (sum({$<OrderPhase_Desc = {'Revenue'}>}NettAmountEUR)+(sum({$<OrderPhase_Desc = {'Backlog'}>}NettAmountEUR))), (sum({$<OrderPhase_Desc = {'Revenue'}>}NettAmountUSD)+(sum({$<OrderPhase_Desc = {'Backlog'}>}NettAmountUSD)))) ,0)

Regards,

Giacomo

[marcus_sommer]

Instead of using "+" it's better to use rangesum() which returned 0 for all non-numeric values or NULL. But in your case you didn't need it then you could write more then one argument within the set analysis:

OrderPhase_Desc = {'Revenue', 'Backlog'}

Further you should check if your expression worked with manually curreny-field. If yes it's a syntax-case, maybe you needs brackets around your field-variable-combination or similar.

- Marcus