Row Level Set Analysis

d33paklala — Tue, 30 Nov 2021 18:17:33 GMT

I have a hierarchy and need to calculate shares.

GeographyHierarchy	Sales
Nation	103
OM	45
OMA	6
OMA01	1
OMA02	2
OMA03	3
OMB
OMB01
OMB02
OMB03
OMC
OMC01
OMC02
OMC03
ON
ONA
ONA01
ONA02
ONA03
ONB
ONB01
ONB02
ONB03
ONC
ONC01
ONC02
ONC03

Territory	District	Region	Nation	Sales
OMA01	OMA	OM	Nation	1
OMA02	OMA	OM	Nation	2
OMA03	OMA	OM	Nation	3
OMB01	OMB	OM	Nation	4
OMB02	OMB	OM	Nation	5
OMB03	OMB	OM	Nation	6
OMC01	OMC	OM	Nation	7
OMC02	OMC	OM	Nation	8
OMC03	OMC	OM	Nation	9
ONA01	ONA	ON	Nation	10
ONA02	ONA	ON	Nation	5
ONA03	ONA	ON	Nation	6
ONB01	ONB	ON	Nation	7
ONB02	ONB	ON	Nation	8
ONB03	ONB	ON	Nation	9
ONC01	ONC	ON	Nation	10
ONC02	ONC	ON	Nation	1
ONC03	ONC	ON	Nation	2

Using Geography Hierarchy as dimension, I want to calculate shares in terr, district and region. For eg. share of territory OMA01 = Sales of OMA01 / Sales of District OMA

Share of district OM = Sales of OM / Sales of Rgion OM

Share of Region OM = Sales of OM / Sales of Nation

I am able to achieve this by If - else condition however it doesn't work in set analysis.For territory shares, I am using

Sum({<District = {'$(=Left(GeographyHierarchy,3))' } >}Sales). This works on a single selection though.

PS : Geography Hierarchy is associated with Terr, District, Region and nation accurately. I.e. Nation under geo hierarchy has all territories, districts, region and Nation value against it and so on.

Re: Row Level Set Analysis

marcus_sommer — Wed, 09 Jun 2021 13:32:49 GMT

A real set analysis worked on a column-level and therefore it's not possible to get row-level sensitive comparisons. You may write something like:

Sum({< Sales = {"=District=Left(GeographyHierarchy,3))" } >} Sales)

but it's quite the same like your if-loop.

- Marcus

Re: Row Level Set Analysis

d33paklala — Wed, 09 Jun 2021 14:20:30 GMT

Thanks Marcus. I would have preferred using the suggestion however it has a high response time and also doesn't return correct results. The hardcoded if - else return accurate data, and is compararatively better in terms of performance

topic Re: Row Level Set Analysis in App Development

Row Level Set Analysis

Re: Row Level Set Analysis

Re: Row Level Set Analysis