topic Re: Set analysis to show when data overlaps in QlikView

Set analysis to show when data overlaps

Fri, 07 Jun 2013 17:32:58 GMT

I have data where one field (Category) could have connections to one or two values in another field (Domain). I would like to make a graph that shows all the Category data (Cases) for a particular Domain. Here's what I have for my chart expression:

Sum({$<Domain = {'Domain1'}>} Cases)

This should sum up all cases where at least one of the Category's Domain is Domain1. And it does...mostly. If I have no selections made, it reports correctly. If I select Domain1, it works too. However if I select another Domain, I would like it to show all Categories that are also Domain1 and it does not.

For example, Category3 is connected to both Domain1 and Domain2. No other category shares those Domains. I expect that if I select just Domain2, the chart would show me just Category3. However, instead it keeps showing me exactly what it showed before depite my selection.

I have attached my test file in case you would like to view it. The chart I am referring to is called "Cases for Domain1."

Any help would be appreciated.

Re: Set analysis to show when data overlaps

Gysbert_Wassenaar — Fri, 07 Jun 2013 17:55:02 GMT

Try Sum({$<Domain = {'Domain1'}>*<Domain =p(Domain)>} Cases)

Re: Set analysis to show when data overlaps

Fri, 07 Jun 2013 21:10:20 GMT

This fixed the issue in my test file attached, however something weird happened when I applied the fix to my real file. They haven't had any differences before.

On the real chart, when I click on a Domain which shares some Categories but not all, the whole pie chart turns green. (The color green which is designated for the 13th color in the graph - r77, g167, b65.) The only dimension it shows is "-" and the numerical value the green dimension represents is all the Categories that share both Domains added together.

Has anyone seen this before? Any ideas what it means or how to fix it?

Re: Set analysis to show when data overlaps

Fri, 07 Jun 2013 21:33:46 GMT

Okay, so I got rid of some NULL values I had loaded intentionally for a field that's related to Category but should not be represented in the graph. This changed the graph color to the 15th color instead of the 13th.

Does that remind anyone of an issue they fixed before?

Re: Set analysis to show when data overlaps

Fri, 07 Jun 2013 23:46:46 GMT

I was able to replicate this in a test document and I believe I have at least isolated the cause. Something to do with the way I am loading the data is leaving NULL values where there shouldn't be.

I want Categories to appear in graphs differently, so I created Nicknames. The reason the whole graph fills up with a color for "-" is because all the Nicknames are coming up as NULL when I choose the other Domain. They are all there when I don't select any Domain, but when I select Domain2 I loose the values

I am uploading my file. Again, any help would be very much appreciated!!!!

Re: Set analysis to show when data overlaps

Gysbert_Wassenaar — Sat, 08 Jun 2013 09:49:00 GMT

You need the nicknames in the other table. See attached example.

Re: Set analysis to show when data overlaps

Sat, 08 Jun 2013 17:25:41 GMT

That did it!

I'm not sure I fully understand why your code works though. I would love to get a better underestanding. Is there a resource I can read about what you did or could you explain a it little, so next time I know what to do?

Thank you so much!