Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
Luminary
Luminary

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

Tags (2)
8 Replies
Highlighted
Contributor III

Re: Sum & If Formula for pivot table

Hi Carolin,

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

Martin

Highlighted
Valued Contributor

Re: Sum & If Formula for pivot table

Caroline,

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

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

Marc.

Highlighted
Luminary
Luminary

Re: Sum & If Formula for pivot table

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

Highlighted
Luminary
Luminary

Re: Re: Sum & If Formula for pivot table

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

Highlighted
Valued Contributor

Re: Sum & If Formula for pivot table

Carolin,

A sample data will help us.

Marc.

Highlighted
Not applicable

Re: Sum & If Formula for pivot table

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
Partner

Re: Sum & If Formula for pivot table

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

Highlighted
MVP & Luminary
MVP & Luminary

Re: Sum & If Formula for pivot table

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