Pivot table not showing totals correctly on a calculated field

[#1]

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?

[sunny_talwar · Accepted solution]

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]

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

)

[#4]

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

[#5]

Thanks Sunny will try this.

[alextimofeyev]

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)

[#7]

Hi Alex,

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

[alextimofeyev]

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.