% expression for totals in Table Chart

[#1]

Hi,

Does anyone know how i can get the % displayed for the totals row, so that it displays the % based on the grand totals?

See screenshot below that shows the table im refering to and the expression for the table.

Any help would be great.

[#2]

I think it is your Only statement that is causing the problem. You have a Target Overall column on your chart that has numerical values, but in your expression, you have a Target Overall field that seems to contain text values. I also don't see Staff Count on your chart, what is Staff Count?

I think if you use: Count(...)/Count(...) you would get a total value. Also, if you did: Count(...)/If(...,Count(...), 1) you would get a value. I can't tell what exactly you are doing, so I can't suggest a correction, but I am pretty sure for the Total, the If is returning false, so Only() is your denominator and since there are multiple values for Target Overall in your total, that is returning a null.

[johnw]

Is each row just the first column divided by the second column? If so, try this:

column(1)/column(2)

No need to repeat the column expressions in the third column.

[#4]

Hi,

Im still having difficulty with this final %. Ive tried 2 scenearios see screenshots below.

EXAMPLE 1 -

Example 2 - (Column 1 / Column 2)

Still both dont product the final total % im looking for any ideas?

Thanks

[johnw]

Use "expression total" for the column 1 / column 2 approach. You explicitly selected "sum of rows", which explicitly tells it to just add up the column instead of doing the division. You want the division. You want expression total.

[#6]

Hi John, thanks for the tip. As the screenshot shows the 'column 1 / column 2' expression total approach still doesnt work.

I think its something to do with the fact that my expressions for completion count and target overall are messing things up.

It seems very fustrating that i cant just use the totals for columns to get an overall %.

Any other tips would be great.

Thanks

[johnw]

That's what I was saying in my previous post. Or are you saying you did that, and it didn't work? Because the new picture you posted gives no indication that you tried what I suggested.

[#8]

Hi John, sorry i think that screeshot was a mistake (see one below).

Yes i used expression total. But this still didnt produce the disired result.

[johnw]

OK, well now your posted example doesn't have a total for the completion count or target overall either. If it doesn't have those numbers, it can't divide those numbers. All of your previous examples showed a total in those columns, which should have allowed the division.

I suspect you have an expression for your first two columns that can't be evaluated at the total level, so you were using sum of rows. When I said use expression total, I didn't mean for THOSE expressions, I meant ONLY for the division expression. I'm also not specifically saying to use sum of rows for the first two columns, because I don't know how they're defined and therefore don't know how they should be totaled. In any case, you need to calculate those totals correctly before you can divide.

HUH! It looks like this does NOT work as I expected when dealing with sum of rows. If the the first two columns can be computed as expression total, the division seems to work. If one of the expressions is computed as sum of rows, the division does NOT work. That seems like a bug to me, though it wouldn't surprise me if QlikTech would say it's working as designed for whatever reason.

So... what seems now to be the case is if your expressions are totalled by sum of rows, the division won't work, and will return null. I didn't realize that, so I'm sorry if I've been leading you astray this whole time.

So you MAY need to fix your expressions to get a total without sum of rows. The general form of that is the same as what we do in pivot tables:

sum(aggr(YourCurrentExpression,Dimension1,Dimension2,etc.))

Attached is an example showing a lot of these different combinations, including the approach that doesn't work and a resolution of that case. Perhaps it will be enough to help you to resolve your problem.

[#10]

Hi John, the reason there were no totals for the columns is because i took it off. It seems like the overall total doesnt appear if the sum columns is ON or OFF.

See below.

Thanks for your example i will look at this now and hopefully resolve the % issue. Its reassuring to know we might have stumbled across a bug.

By the way if i cant reolve this total % by expression i will have to use Average of expresssions (which howver does work) although this itn't a true total %.