5 Replies Latest reply: Apr 19, 2018 6:35 AM by vishal raina

# Creating aggregated dimension from another dimension

Hi all,

Need urgent help on this one.

Lets say i have data in the below form :

 items Week Sales a 1 200 a 2 300 a 3 400 b 1 100 b 2 250 b 4 250 c 1 100 c 2 100

From this data i need to create a table which should be like this.

 Field Required Sum(Sales) only a 900 only b 600 both a & b 850 neither a nor b 200

The 'Field Required' needs to be aggregated on weeks & items.

ex, In the required table 'only a' should have weeks which are present only in a i.e.,weeks 1,2,3 of a.

similar for 'only b'.

'both a & b' should have weeks which are common in a & b i.e., weeks 1 & 2 of both a & b.

likewise for 'neither a nor b', weeks without a & b should be present, i.e., c in this case .

Currently i'm using the below  expression for the Field Required:

=if(Aggr(items = 'a',WEEK,items),'only a',

if(Aggr(items = 'b',WEEK,items),'only b',

if(Aggr(items = 'a' and items = 'b',WEEK,items),'both a & b',

if(Aggr(items <> 'a' and items <> 'b',WEEK,items),'neither a nor b'))))

With this i'm able to get only a & only b, but unable to get the other 2.

Any help on this would be great.

This?

• ###### Re: Creating aggregated dimension from another dimension

Sunny,

Thanks for your help, this does work. Need to add one more column. which would take the minimum of the derived sales and divide it to the individual sales. ex: only a would become 4.5 (900/200) & likewise for others. Trying to figure out how do we get the minimum sales from here.

• ###### Re: Creating aggregated dimension from another dimension

May be try this

Pick(Match(ValueList('only a', 'only b', 'both a & b', 'neither a nor b'), 'only a', 'only b', 'both a & b', 'neither a nor b'),

Sum({<items = {'a'}>}Sales),

Sum({<items = {'b'}>}Sales),

Sum({<Week = p({<items = {'a'}>})*p({<items = {'b'}>}), items = {'a', 'b'}>}Sales),

Sum({<items -= {'a', 'b'}>}Sales))

/

RangeMin(

Sum({<items = {'a'}>}Sales),

Sum({<items = {'b'}>}Sales),

Sum({<Week = p({<items = {'a'}>})*p({<items = {'b'}>}), items = {'a', 'b'}>}Sales),

Sum({<items -= {'a', 'b'}>}Sales))

• ###### Re: Creating aggregated dimension from another dimension

Thanks so much, this works perfectly.

• ###### Re: Creating aggregated dimension from another dimension

You could also have each measure separately in a Pivot Table like this:

You simply add a dimension ='SALES' with the label Sales then you add the four measures as you can see in the first table above. After that you put the dimension on a column and the measures as rows in a Pivot Table.