Problem with linear gauge representation in pivot table

[#1]

Hi Experts,

I need your help desperately to find a solution to my problem. Here's the explanation:

Requirement: I want to represent a particular metric as linear gauge in a pivot table with 3 dimensions.

Dimensions: Country, Office, Employee

Standard metrics: Actual Spend, Expected Spend, Budget

Calculated metrics: 1. % Actual Spend = Actual Spend/Budget

                             2. % Expected Spend = Expected Spend/Budget

What I did to get this?

1. Calculate and represent all the calculated dimensions in the pivot table as TEXT.

2. I'm able to define the parameter to get the linear gauge.

          Segments      Lower Bound  

          Segment 1     0.0 (RED Colour)

          Segment 2     =sum(Expected Spend)/Sum(Budget) (Green Colour)

          Min is set as 0.0 and Max is set as 1.0

3. % Actual Spend is shown correctly (Needle) and looks correct.

What's wrong?

Segment 2 doesn't seem to work. Well, it actually does show up but the position where the segment 2 should begin does not change.

It is always fixed at one position and change in dimensionality does not affect the position of segment 2.

I have replicated the issue in the attached file for your reference. Any help would be appreciated.

Thanks, Kiran

[#2]

Any ideas folks? I've not been able to resolve this issue.

[jonathandienst]

Hi

You need to clear the check in "Autowidth Segments"

Regards

Jonathan

[#4]

Hi Jonathan,

Thanks for your response.

I cleared the "Autowidth Segments" and then enabled the condition to dynamically assign calculated and assign segments. This is where the problem is occuring.

Did you get a chance to download the file I attached? You will see that Autowidth Segments is not checked and I've used a formula in the segments.

Thanks,

KG

[richard_simpson]

Hi Kiran

I think I have experienced a similar problem today....

I have placed a linear gauge into a straight table expression, and I only have a single dimension: staff member.

I have unticked the autowidth segments option and specified expressions for the lower value of segments 2 and 3(e.g. sum[a value that appears on multiple transactions which will be associated to various staff - always just one member of staff per transaction])

When I view the table, the segments all appear in the same place for each member of staff, and their individual totals never result in any of them reaching segment 2, let alone segment 3.

But they should!

The reason that they don't is that the sum(xxx) expression to derive the lower bounds of segment 2 and 3 do not appear to be taking the dimension of the straight table into consideration i.e. they are all set to the overall total for every member of staff.

So of course, individually, no one member of staff can reach segment 2.

But when you look at the total row of the table, the linear gauge is shown correctly: with the totals for all staff reaching well into segment 2, almost into segment 3!

If I select one member of staff, then the only row shown (apart from the total row) does show correctly... so suddenly that selected member of staff can be seen to have reached segment 2 or 3, and the total is identical.

De-select the member of staff, and that person goes back to hardly progressing through segment 1.

Assuming that for some reason the linear gauge is not impacted by the straight table dimension, I tried usisng an AGGR statement in the expressions to set the lower bounds of 2 and 3.

I think this is along the right lines.... but I haven't got it working yet.

I hope that helps a little? I know it isn't much, but hopefully it is a steer into what is the cause?

Good luck

Richard

[#6]

Hi Richard,

Thanks for your reply.

We both have gone through the same steps to analyze and find a solution to this problem. I tried using dimensionality() and AGGR functions to achieve this but it did not work.

After many days of suffering, I raised a support ticket to see if Qlikview experts could help me resolve this but I got the below response from them:

Hello Kiran,

We have been looking into the possibilities of having individual line gauges in a straight/pivot table, but unfortunately it seem there that there is no possibilities for that. What you would need is to have them as different object in your application or some other solution, but getting individual line gauges inside a table like in your example doesn't seem possible at this moment.

Please let us know if further action is needed.

So I had no other option but to look for an alternative.

- Kiran

[richard_simpson]

Hi Kiran

Thanks for your reply.

Well that saves me raising a ticket with suppoort, which was what I was about to do!

OK, I'll consider other options too....

I have come across a similar loking solution, but not as nice, where people are using the old QV methods, before in line gauges were possible...

They are simply adding a column which displays a hex value, repetitively (depending on value to be displayed) with a formula for the text colouration to determine if it shows as red, amber or green.

I'll give that a go, but will have to be careful that the width of the displayed character doesn't get too large!

Thanks again

[#8]

Max value (Gauge Settings) must be bigger than Segment Lower Bound value. !