topic Same field shows different value in 2 charts in App Development

Same field shows different value in 2 charts

levente81 — Wed, 16 Aug 2023 14:38:24 GMT

Hi,

I am facing an exciting enigma in Qlik, and I hope someone can advise a solution / workaround.

I have a field with lots of values that I categorize into 5 buckets with using simply nested if statements - dual() values assigned for those groups.

Then I am expected to use this data in a pivot chart where the main dimension is a valuelist() and the sub dimension is one of 3 bucket fields depending on the valuelist() dimension's value.

It worked good, when I created the logic several weeks ago and now I got a complaint that something was wrong with this pivot table - for some reason one of the bucket fields are not properly displayed => the "label" part of the dual value is not properly displayed: dual('0',1) is displayed as 1, next bucket is dual('1',2), then dual('range1',3) etc.

If I use the same field and filter the "parent" field to 0 where the bucket value shall be this dual('0',1) then I get the proper 0 displayed, while pivot remains corrupted...

Do you have any idea, workaround for such bug?

Thanks and best regards,

Levente

Re: Same field shows different value in 2 charts

Oleg_Troyansky — Wed, 16 Aug 2023 14:50:36 GMT

It's really difficult to guess what might be wrong there. If you can, post a sample app that shows this behavior.

Re: Same field shows different value in 2 charts

marcus_sommer — Wed, 16 Aug 2023 15:46:56 GMT

I don't think that there is any bug else that the data have changed and aren't unique anymore - in regard to the applied dual(). This means it's quite likely that there are now multiple string-values for the same numeric value.

Beside this I suggest to replace the valuelist() with the field-values from an island-table. It wouldn't only simplify the chart-dimension else even more the expressions and an island-table mustn't be just a single field else it might be several ones with a grouping and extra information. Further such native fields could now be used within TOTAL statements and/or aggr() constructs. Probably there are some more benefits ...

Re: Same field shows different value in 2 charts

levente81 — Thu, 17 Aug 2023 12:55:28 GMT

Hi Marcus,

I do not think it is a data update issue that certain dimension is displayed:

- properly in a filter pane

- erroneously in a pivot table

at the same time...

If plain field value is 0 then bucket field value is dual('0',1)

If plain field value is 1 then bucket field value is dual('1',2)

... this logic is used to create in DLE the bucket field that is displayed erroneously in pivot table. I see no reasonable situation where such expression shall result dual('1',2) - please check the screenshot: I have a filter on the plain field...

Re: Same field shows different value in 2 charts

marcus_sommer — Thu, 17 Aug 2023 14:04:03 GMT

That's not mandatory the same because the filter pane showed only distinct values from a single field against a certain state while the pivot may enforce a cartesian view and consider multiple states - and both may also have different sorting-options.

Further both types of objects are limited to the capability to show all field-values and their relationship against each other. More suitable would be to use a table-box which also includes unique ID's from all related tables. If none exists they could be created with recno() and/or rowno(). Because of the fact that you are dealing with dual() you may further add a table-chart to be able to query the dual-fields with num() and text() on the lowest granularity and maybe also with count() and concat() on any higher views.

Re: Same field shows different value in 2 charts

levente81 — Wed, 30 Aug 2023 13:17:52 GMT

It was a temporary bug - it got solved without any modification.