Trouble synchronizing the figures displayed in different objects ...

[datanibbler]

Hi,

I have, in an app I am currently building, two objects:

- A straight_table chart and

- a gauge_chart

and I am trying to synchronize them in that they should display as exactly as possible the same figures.

Logical, isn't it?`Sounds easy, too - well, so one should think ...

It is not. The figures were even quite far apart. I have by now removed one calculated dimension from the chart that the gauge did not have and removed the tick "suppress NULL values" for another dimension and realized that the figures came closer.

To do that, I calculated a flag_field in one of my tables to tell whether or not a specific record in the table is linked to any in the other table. I used an EXISTS() function for that to check whether the keyfield used to link the two tables exists in a copy of the keyfield in the other table. That seems to work quite well.

Now, the figures are quite close - the chart shows a final ratio of 71.33 % and the gauge shows 70.2 % which isn't bad - but still not ideal if there is any way to fix it. That can't be rounding errors - well, several rounding errors could stack up to that difference ...

Can you tell me what that could be due to?

Thanks a lot!

Best regards,

DataNibbler

[jagan]

Hi,

In Straight table try like this

Properties -> Expression -> Select Expression Total then it should match with the Gauge chart.

Hope it helps you.

Regards,

Jagan.

[marcus_sommer]

Hi DataNibbler,

the differences look a bit to high for rounding-differences - I would rather assume that your divisor slightly differ maybe because some empty or NULL value will be counted without a dimension-relation and ignored by using the dimension by enabling the hide-option on NULL values or something quite similar. I think if you separate those divisor in both expressions you will see a difference between them and with a few selections on various fields you could then localize the reason.

- Marcus

[datanibbler]

Hi jagan,

yes and no - I have spotted the difference: One figure - I have the total sum on top of that expression_column now - differs by 11 items - I have put a textbox next to my gauge_chart to display the figures that the gauge works with.

The difficulty now is to find out exactly what "set_expressions" (of sorts, in the form of dimensions, "suppress NULL" ticks and such) are encompassed in the sum that my table_chart displays so I can copy them in the expressions used by the gauge.

That's why I have made it a standard for myself and all my possible successors to avoid having IF() constructs and such in the dimensions of a chart - one can tweak a chart pretty much, no problem, but then when you put a simple textbox or a second chart beside it, it shows different figures because of all those special settings and you have a credibility_problem ...

Best regards,

DataNibbler

[jagan]

HI,

If you want to implement supress Null in text object expression then try like below

Sum({<DimensionName-={'*'}>} MeasureName)

-={'*'} will exclude Null values.

Regards,

Jagan.

[marcus_sommer]

Quite right - to fix the causes is always better than to handle their impacts.

- Marcus

[datanibbler]

Hi Marcus,

yep. I've spotted what is a likely enough reason - that'as another point where I have actually not kept the standard I have mentioned in my above post ...

There is an IF() construct in all of my dimensions (always the same), in addition to the "suppress NULL" ticks in two of them - it goes like

>> IF(Art_Lager <> 'nonexistent', [dimension] <<

=> Now I have to put this into a SET_expression that I can implement in the expressions that my gauge works on.

Before, it was

>> COUNT(DISTINCT Track) <<

So I guess now it has to be

>> COUNT({$<Art_Lager = {"$(= '<>' & 'nonexistent')"}>} DISTINCT Track) <<

Right? I know that could be written more elegantly, but then the expression_editor doesn't recognize it anymore and underlines it ...

[jagan]

Hi,

Try this

COUNT({$<Art_Lager -= {'nonexistent'}>} DISTINCT Track)

It will show error new -= but it works.

Regards,

Jagan.

[datanibbler]

Hi jagan,

I don't quite understand - that >> -= << excludes something from the set of data, okay. But the *? How does that exclude NULL values?

Anyhow, I'd rather not use the >> -= << because the expression_editor (in QV 11 SR 2) does not recognize this and underlines it - and if there are any other formulas following this one, they are not checked anymore so one cannot be sure about the exact location of potential errors ...

[datanibbler]

Hi jagan,

see my above answer to your post - I am quite confident this could work, but then I have a problem with the expression_editor and as I always try to keep things as legible and understandable as possible for myself and mainly for others, be it temporary backups or successsors, I'd rather not use this option ...