Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
datanibbler
Champion
Champion

Trouble synchronizing the figures displayed in different objects ...

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

41 Replies
jagan
Luminary Alumni
Luminary Alumni

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.

marcus_sommer

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

- Marcus

datanibbler
Champion
Champion
Author

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

datanibbler
Champion
Champion
Author

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.

marcus_sommer

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

datanibbler
Champion
Champion
Author

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

datanibbler
Champion
Champion
Author

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

marcus_sommer

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

datanibbler
Champion
Champion
Author

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

datanibbler
Champion
Champion
Author

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?