Announcements
cancel
Showing results for
Did you mean:
Luminary Alumni

## Sum & If Formula for pivot table

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

8 Replies
Specialist

Hi Carolin,

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

Martin

ECG line chart is the most important visualization in your life.
Anonymous
Not applicable

Caroline,

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

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

Marc.

Luminary Alumni
Author

\$(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.

Luminary Alumni
Author

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

Anonymous
Not applicable

Carolin,

A sample data will help us.

Marc.

Not applicable

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)

Partner - Contributor III

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

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

Community Browser