topic Re: Trouble synchronizing the figures displayed in different objects ... in QlikView

Trouble synchronizing the figures displayed in different objects ...

datanibbler — Fri, 29 Jan 2016 08:24:55 GMT

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

Re: Trouble synchronizing the figures displayed in different objects ...

jagan — Fri, 29 Jan 2016 08:29:18 GMT

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.

Re: Trouble synchronizing the figures displayed in different objects ...

marcus_sommer — Fri, 29 Jan 2016 08:35:24 GMT

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

Re: Trouble synchronizing the figures displayed in different objects ...

datanibbler — Fri, 29 Jan 2016 08:35:47 GMT

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

Re: Trouble synchronizing the figures displayed in different objects ...

jagan — Fri, 29 Jan 2016 08:40:16 GMT

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.

Re: Trouble synchronizing the figures displayed in different objects ...

marcus_sommer — Fri, 29 Jan 2016 08:41:09 GMT

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

- Marcus

Re: Trouble synchronizing the figures displayed in different objects ...

datanibbler — Fri, 29 Jan 2016 08:42:26 GMT

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 ...

Re: Trouble synchronizing the figures displayed in different objects ...

jagan — Fri, 29 Jan 2016 08:45:11 GMT

Hi,

Try this

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

It will show error new -= but it works.

Regards,

Jagan.

Re: Trouble synchronizing the figures displayed in different objects ...

datanibbler — Fri, 29 Jan 2016 08:45:32 GMT

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 ...

Re: Trouble synchronizing the figures displayed in different objects ...

datanibbler — Fri, 29 Jan 2016 08:47:14 GMT

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 ...

Re: Trouble synchronizing the figures displayed in different objects ...

jagan — Fri, 29 Jan 2016 08:48:07 GMT

HI,

It is a bug in Qlikview from QV9, it shows are error but it works as expected.

-={'*'}

* means everything except null

-={'*'} - From the current selections exclude null in the expression.

Regards,

Jagan.

Re: Trouble synchronizing the figures displayed in different objects ...

marcus_sommer — Fri, 29 Jan 2016 08:48:23 GMT

The syntax-highlighter isn't always correct - if your expression worked you could ignore the error-message.

- Marcus

Re: Trouble synchronizing the figures displayed in different objects ...

datanibbler — Fri, 29 Jan 2016 08:53:36 GMT

Hi Marcus,

yes, of course I could ignore it ...

Then I'll just have to put a comment directly in the expression about this. Still I'd rather not, but I'll try out lateron.

Okay. This works - now it's down to 1 item difference which makes for a difference in ratio which will probably not show with just 2 decimal places ... I wonder where that is - I don't have any dimensions or stuff where 1 item is different from all the rest ...

Re: Trouble synchronizing the figures displayed in different objects ...

datanibbler — Fri, 29 Jan 2016 09:07:03 GMT

Hi,

now I have a different problem - well, it's the same, kind of ...

The numbers of items (the two numbers that make up my ratio) are now the same between the table_chart and the gauge - one differs by 1, but I guess that can be ignored ... And the ratio is 70.15% (I have recalculated it) and the gauge correctly shows 70.2% - the table_chart, however, shows a ratio of 71.33%.

Can that be due to rounding? I guess it can - the expression in the chart is of course calculated for every single line and that figure on top is the average.

Re: Trouble synchronizing the figures displayed in different objects ...

marcus_sommer — Fri, 29 Jan 2016 09:07:39 GMT

Hi DataNibbler,

the reason could be that you counts a keyfield or that there is any empty field-value - often you find this only with a tablebox of your fields and reducing the dataset by various selections to exclude those data-areas which are fitting until you comes to those where the unexpected must be and there are less enough data to check them manually.

- Marcus

Re: Trouble synchronizing the figures displayed in different objects ...

datanibbler — Fri, 29 Jan 2016 09:38:23 GMT

Hi Marcus,

yes, a tablebox is usually the best way to find those little bugs. However, right now I have other more pressing things to do - to be honest, it is mainly me striving to get this 100% correct, nobody else will really care about 1% more or less - not in this matter and also because there are many more factors to do with the underlying data because of which the data shown in this app can never be absolutely correct anyway.

I'll close this thread now, but I guess I will have another look at this when there is time - some time in Q3 maybe ...

Best regards,

dataNibbler

Re: Trouble synchronizing the figures displayed in different objects ...

datanibbler — Tue, 02 Feb 2016 12:54:45 GMT

Hi Marcus,

sorry to bring this up again when it's already kind of solved - I think somebody here once told me a way (I think it was you) to build a set_expression identical in effect to that > -= < construct which the editor does recognize - that has the advantage that the editor does not stop checking any syntax at that point, but it also checks the subsequent statements.

I tried, it was something like (an expression in the set_expression) >> $(= '<>' & [not_wanted_value]) << - well, almost, but that does not work. Say I want to replace the working set_expression >> Art_Lager -= {'nonexistent'} <<

Thanks a lot!

Best regards,

Datanibbler

Re: Trouble synchronizing the figures displayed in different objects ...

marcus_sommer — Tue, 02 Feb 2016 13:30:30 GMT

Hi DataNibbler,

no, I wasn't it then I would use simply the not recognized syntax of -=. I could imagine that there are possibilities with '<>' syntax, maybe something with two sets like:

< Art_Lager = {'*'} > - < Art_Lager = {'nonexistent'} >

but I would rather use a function like:

< Art_Lager = {"$(=if(match(Art_Lager, 'nonexistent'), false(), true()))"} >

- Marcus

Re: Trouble synchronizing the figures displayed in different objects ...

datanibbler — Tue, 02 Feb 2016 13:47:24 GMT

Well, that doesn't seem to work. I will keep on trying, however. The editor stopping at that statement and not checking the potentially long remainder of an expression is just too annoying.

Best regards,

DataNibbler

Re: Trouble synchronizing the figures displayed in different objects ...

datanibbler — Wed, 03 Feb 2016 09:55:00 GMT

Hi,

I'm still puzzled by the two objects in this app - a gauge and a table_chart - displaying different figures (the usage_rate of capacities in %).

The table_chart has a number of dimensions, but no "suppress NULL" ticked, no general limitations,

so I really wonder what can cause the table_chart to behave differently.

Also, it seems that individual items - when I select just one row from that table_chart - it seems to be correct, the two objects then show the same - so it must be something about how QlikView calculates that average - the formula in the table_chart calculating the usage_rate of every single line_item calculates using two of the previous expressions, something that can be done in table_charts to simplify the formulas.

There are of course capacities which are not used and thus have a rate of 0 - that enters into the average of course, but it also does so in the gauge, no?

There are other items which do not figure in our new Capacities_database and thus have no usage_rate at all - those do not enter into the average, do they?