topic Re: How add dummy value as grouping to Dimension in QlikView

How add dummy value as grouping to Dimension

Mon, 14 Mar 2016 17:05:03 GMT

Hi all, hoping someone can suggest an approach for this. As a simple example what would be the best practice to group a specific set of dimension values and treat them as just another dimension, i.e. all Big companies grouped under single dimension record.

I also have a complication in that I need to show the Company Dimension across the top and the measures in rows (previous post for this: Sum of Columns in Pivot table)

Many thanks in advance!

Re: How add dummy value as grouping to Dimension

sunny_talwar — Mon, 14 Mar 2016 17:11:55 GMT

Do you have a sample you can share?

Re: How add dummy value as grouping to Dimension

Mon, 14 Mar 2016 17:17:54 GMT

Sure, here you go...

Re: How add dummy value as grouping to Dimension

sunny_talwar — Mon, 14 Mar 2016 17:36:07 GMT

Are you looking to see something like this?

I used a calculated dimension instead of Company -> =Left(Company, Index(Company, ' ', 2))

or are you looking to see them separately and then grouped?

Re: How add dummy value as grouping to Dimension

Mon, 14 Mar 2016 18:16:32 GMT

Hi, ideally I need to see them in the same chart, and the names are simply the actual names of companies so can't use Left(Company, Index(Company, ' ', 2)) but hard coding the names of the group is ok if that helps as the names defined in the group shouldn't change.

Re: How add dummy value as grouping to Dimension

maxgro — Mon, 14 Mar 2016 18:54:21 GMT

T1:

Load * inline [

Company,No of Bike Sale, No of Car Sale

Big Company A,5,20

Big Company B,24,3

Small Company C,8,2

Small Company D,15,1

];

T11:

load Company as CompanyGroup, Company Resident T1;

load left(Company, index(Company, 'Company') +len('Company')) as CompanyGroup, Company Resident T1;

Re: How add dummy value as grouping to Dimension

Tue, 15 Mar 2016 12:09:54 GMT

Hi, thanks for this, although this relies on Company being in the name I can probably get round that by prefixing the actual company names with something however how would I go about showing all the individual companies along with the groupings in the same chart or would I need to create two separate charts and try aligning them together? also what if I only want to show Big Company as an extra grouping?

Many Thanks!

Re: How add dummy value as grouping to Dimension

maxgro — Tue, 15 Mar 2016 18:21:23 GMT

you can use company group as dimension when you need the extra group (Big Company, Small Company)

or Company as dimension for "standard" companies

Re: How add dummy value as grouping to Dimension

Wed, 16 Mar 2016 10:08:18 GMT

Good point, many thanks will give it a try!!!