Combine multiple dimensions into one

[#1]

Hi all,

I need to be able to show a box plot where the user can do Peer comparison. This means I need to be able to  have up to three different fields in one dimension and the user can select different items from these different fields. At the moment I have built it by creating a new Concatenated field in the load statement, then I use a variable to combine all the user selections and use the match function to compare this against the combined field. It works but it is slow so I was wondering if anyone knew of a more efficient way of doing it?

Any suggestions would be helpful

Thanks

Grace

[Gysbert_Wassenaar]

Peer comparison sounds like what some call comparative analysis. So perhaps you can use alternate states like mentioned in this blog post: http://community.qlik.com/blogs/qlikviewdesignblog/2013/02/26/why-comparative-analysis

There's a nice example in the What's New in Qlikview 11 demo (link in the blog) on the Product Grouping tab. The file should also be in the Examples\Documents folder in the installation folder where Qlikview is installed.

[#3]

Thanks Gysbert, I have looked into comparative analysis but it seems to compare buckets to each other. So for example say I wanted to do a comparison between two countries and three regions, using comparative analysis I would get one box plot for the countries and one for the regions but I need one for each item.

[Gysbert_Wassenaar]

Ok, so you have two dimensions, countries and regions. And you want to be able to compare a country of your choice with a region of your choice. For that kind of analysis the values of the countries and regions should be in one dimension. That means you have to create a field for that in the script. I assume your countries are part of regions (or vice versa), i.e. they are somehow related already in your model. In that case you can create a new table with the fields ReportRegion and Country:

ReportRegions:

load Region as ReportRegion, Country Resident RegionsAndCountriesTable;

load Country as ReportRegion, Country Resident RegionsAndCountriesTable;

You can then use the ReportRegions field to select the values you want to compare and as dimension in your charts.

[#5]

That's what I have done already but it's slow so was wondering if there was another way to do.

[Gysbert_Wassenaar]

That table shouldn't slow things down much. Unless you have about a hundred thousand countries. Perhaps the problem is caused by expressions in the front end. Can you post the offending document? You can scramble data that's confidential if necessary. See Preparing examples for Upload - Reduction and Data Scrambling

[#7]

I do have to pull in millions of rows for the new concatenated field so that is probably it. It's ok, my solution works fine, I just wanted to see if I was missing a much simpler solution.