# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

## Pivot table not showing totals correctly on a calculated field

Qilk Sense Newbie.

I have a pivot table where I have placed a total monetary sales value with the dimensions of year, month, source (store type) and sub source (store name).

The field "Order Total GBP (FBA Fix)" is calculated measure.

if(Source='AMAZON FBA',

(Sum({\$<Source-={'AMAZON FBA'}>} [Line total_GBP]))

,

Sum({\$<Source-={'AMAZON FBA'}>} [Order total_GBP])

)

But when I check the totals for the month the total is not adding  up.

Is this an error in Qilk? Or do I need to do something in the calculated field for the values total correctly?

1 Solution

Accepted Solutions
MVP

## Re: Pivot table not showing totals correctly on a calculated field

Sum(Aggr(

if(Source='AMAZON FBA',

(Sum({\$<Source-={'AMAZON FBA'}>} [Line total_GBP]))

,

Sum({\$<Source-={'AMAZON FBA'}>} [Order total_GBP])

)

, [Processed Year], [Processed Month], Source, SubSource))

7 Replies
MVP

## Re: Pivot table not showing totals correctly on a calculated field

Sum(Aggr(

if(Source='AMAZON FBA',

(Sum({\$<Source-={'AMAZON FBA'}>} [Line total_GBP]))

,

Sum({\$<Source-={'AMAZON FBA'}>} [Order total_GBP])

)

, [Processed Year], [Processed Month], Source, SubSource))

Esteemed Contributor II

## Re: Pivot table not showing totals correctly on a calculated field

Isn't your if () condition and set analysis contradicting each other! How can source be = To and -= for the same line

What are you trying to do with the if?

I assume you want to sum different field based on source !

That would be something like

Sum (if(Source='AMAZON FBA',

[Line total_GBP],[Order total_GBP])

)

Not applicable

## Re: Pivot table not showing totals correctly on a calculated field

Thanks for pointing out. Yes indeed I shortened the script from the original to make the question simpler also the reason for not writing the formula as you have (which would be better if I did not have other conditions).

Not applicable

## Re: Pivot table not showing totals correctly on a calculated field

Thanks Sunny will try this.

Contributor II

## Re: Pivot table not showing totals correctly on a calculated field

Christopher,

In addition to other comments, I would recommend to avoid using if() in measures - it slows things down. Instead, you can do this:

Sum({\$<Source={'X'}>}FieldName1)+Sum({\$<Source-={'X'}>}FieldName2)

Not applicable

## Re: Pivot table not showing totals correctly on a calculated field

Hi Alex,

Does the + between the two act like an or statement?

Contributor II

## Re: Pivot table not showing totals correctly on a calculated field

Christopher,

no, it just adds together results of two expressions. So, on the aggregate it would calculate the sum of FieldName1 for the first set (those records that have Source = 'X') and the sum of FieldName2 for the other set (not 'X'). On the record level though, one of the expressions would always result in 0.