topic Re: Sum & If Formula for pivot table in QlikView

Sum & If Formula for pivot table

carolin01 — Mon, 01 Dec 2014 13:23:39 GMT

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

Re: Sum & If Formula for pivot table

mato32188 — Mon, 01 Dec 2014 13:31:29 GMT

Hi Carolin,

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

Martin

Re: Sum & If Formula for pivot table

Anonymous — Mon, 01 Dec 2014 13:31:49 GMT

Caroline,

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

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

Marc.

Re: Sum & If Formula for pivot table

carolin01 — Mon, 01 Dec 2014 13:35:55 GMT

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

Re: Re: Sum & If Formula for pivot table

carolin01 — Mon, 01 Dec 2014 13:41:06 GMT

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

Re: Sum & If Formula for pivot table

Anonymous — Mon, 01 Dec 2014 13:43:48 GMT

Carolin,

A sample data will help us.

Marc.

Re: Sum & If Formula for pivot table

Mon, 01 Dec 2014 14:08:45 GMT

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)

Re: Sum & If Formula for pivot table

giacomom — Mon, 01 Dec 2014 14:19:30 GMT

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

Re: Sum & If Formula for pivot table

marcus_sommer — Mon, 01 Dec 2014 15:32:14 GMT

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