Qlik Community

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).

Pivot Table.png

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.

Pivot Table Excel.png

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
MVP

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

Try adding Sum(Aggr())

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
MVP

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

Try adding Sum(Aggr())

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))

vinieme12
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.

alextimofeyev
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?

alextimofeyev
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.