Create a Dimension with indents

[Anonymous]

Hi All,

I got a request to create a dimension in below format. I know about the Tree view in List box but that is not what I am looking for. I want to use the output dimension as a Dimension in a Straight table with similar view.

Input Data: (Region - EU, Territory - Benelux/Central Europe, Country - Belgium/Netherlands/Germany etc)

Region-Territory-Country

EU

EU-Benelux

EU-Benelux-Belgium

EU-Benelux-Netherlands

EU-Central Europe

EU-Central Europe-Germany

EU-Central Europe-Poland

Output required:

Region-Territory-Country

EU

Benelux

Belgium

Netherlands

Central Europe

Germany

Poland

Thanks

Karthik

[Frank_Hartmann]

like this?

[jonathandienst]

You can get this type of view in a pivot table using Style | Indent Mode. This assumes that he data models has associations between the header levels. If you have a single field, you will some load logic to split into 3 fields.

Or you can prefix the name with spaces in a straight table. But you will not able expand or collapse like in a pivot. For example a calculated dimension like:

=Repeat('  ', SubStringCount([Region-Territory-Country], '-') & [Region-Territory-Country]

[jonathandienst]

Or

=Repeat('  ', SubStringCount([Region-Territory-Country], '-')) &

SubString([Region-Territory-Country], '-', SubStringCount([Region-Territory-Country], '-')

[Anonymous]

Not exactly.

I know this Tree view option. Let me try the solution given by Jonathan

[sudeepkm]

slight modification to Jonathan's expression.

=Repeat('  ', SubStringCount([Region-Territory-Country], '-')) & SubField([Region-Territory-Country],'-',SubStringCount([Region-Territory-Country], '-')+1)

For below data:

Region-Territory-Country

EU

EU-Benelux

EU-Benelux-Belgium

EU-Benelux-Netherlands

EU-Central Europe

EU-Central Europe-Germany

EU-Central Europe-Poland

USA

USA-NY

USA-NY-NewYork

USA-NY-Buffalo

USA-CT

USA-CT-Hartford

USA-CT-Berlin

output:

[Anonymous]

Yes, it worked. Almost there

Can we show EU in the middle and Benelux without indent and Beglium with Indent.

Like below:

EU

Benelux

Belgium

Netherlands

[sudeepkm · Accepted solution]

sure. This can be done in few ways. One way is given below.

Please follow these steps.

Create a variable as below.

vlen

=Max(aggr(len([Region-Territory-Country]),[Region-Territory-Country]))

Then change the Dimension as

=if(SubStringCount([Region-Territory-Country], '-')=0,

Repeat('   ',Round(vlen/4))&[Region-Territory-Country],

Repeat('  ', SubStringCount([Region-Territory-Country], '-')) & SubField([Region-Territory-Country],'-',SubStringCount([Region-Territory-Country], '-')+1))

[sudeepkm]

also instead of '  ' in Repeat function it is better to use chr(32) for space.

[Anonymous]

Countries are randomly segregated into different Territories in my file.

Am I missing any sorting