Traffic Light Gauge Chart issue with if function

[#1]

Good afternoon,

I have an issue with a Traffic light gauge chart.
I know how to set up gauge charts. My problem is, when on the Expression tab I use the Total Mode "Expression Total" the gauge works perfectly and according to the segment bounds I defined but the correct result and the one I want to use is the one from the Total Mode "Sum of lines" and the Gauge continues to work based on the "Expression Total" result.

Tried to go around this creating a count formula but got the same issue and the AGGR function doesn't help either.
Any ideas?

Thanks in advance

Sergio

[Gysbert_Wassenaar]

Can you post a qlikview document that demonstrates the problem?

[#3]

Hi Gysbert,

Thank you for your reply and sorry for my late reply.

I built a simple report attached to replicate my issue.

The issue is actually a bit different than what I thought in the first place. The Gauge bounds are not working upon the formula total that I input (which is 200) but upon the first line result (40). Don't think there is anything wrong with the formula as it returns the result I want but don't manage to put the Gauge working on that total.

Any ideas to go around this?

Thank you

[Gysbert_Wassenaar]

The dimensions you added to the gauge chart don't do anything unless you enable the Trellis option. Otherwise the dimensions are ignored. See attached qvw.

[#5]

Good Morning Gysbert,

Thank you for your support, I see what you mean about the trellis, you created one Chart per line result, although what I want is a chart for the total of the column "Difference" = 200 as per Total Mode: Sum of rows, how can I do that?

Thanks again

Sergio

[Gysbert_Wassenaar · Accepted solution]

Remove all dimensions from the gauge chart and use this expression: =sum(aggr(fabs(sum(Balance1)-sum(Balance2)),Office))

[#7]

Brilliant, thats what I was looking for

Tried to use the AGGR before but obviously didn't use it properly, I think I didnt remove the dimensions..

Thanks a lot!