Retaining totals on a straight table

[#1]

I'm sure this is really simple but its really frustrating.

You will see on image below we have different cost codes and budget values. The third column we have created to show % this is of total budget - naturally these add up to 100%:

When we select one of the cost codes we want to retain the % of total budget value, e.g 74-7430 - 4.6%. However, Qlikview changes it to 100%:

This is to do with the calculation and options which are as follows:

Can anyone please help in how we can change settings so it retains individual % of total budget regardless of selection?

Chris

[sunny_talwar]

Try something like this:

If(Sum([Budget 15/16]) > 0, Sum({<[Cost Code]>}[Budget 15/16])/Sum({<[Cost Code]>}TOTAL [Budget 15/16]))

[dominicmander]

Hi Chris,

Try Sum([Budget 15/16])/Sum({$<[Cost Code]=>}Total [Budget 15/16])

What this is doing is ignoring all selections in the Cost Code field for the denominator.

[morganaaron · Accepted solution]

If you just want to retain the %age ignoring selections, use:

Sum([Budget 15/16])/Sum({1} TOTAL [Budget 15/16])

The {1} will stop selections affecting that part of the expression.

[sunny_talwar]

Elegant, only thing I would suggest is looking at the whether to use {1} or {<[Cost Code]>}

{1} wouldn't change the denominator based on any selection

{<[Cost Code]>} wouldn't change the denominator based on selection in just Cost Code, other fields such as region etc would still change the %age.