2 Replies Latest reply: Aug 7, 2018 8:06 PM by Agostino Cardella

# Measure divided between (team of) two dimensions / columns

Hello

I have an input table with two dimensions <am1 and am2> and one measure <sales>, like the following:

 am1 am2 sales pluto 7 pluto pippo 4 pluto topolino 10 pippo 1 pippo pluto 6 pippo topolino 2 topolino 5 topolino pluto 4 Total 39

I would like to get an output table, in the app, like the following:

 am am team sales sales by am pluto pluto 7 7 pluto pluto - pippo 4 2 pluto pluto - topolino 10 5 pluto pippo - pluto 6 3 pluto topolino - pluto 4 2 pippo pippo 1 1 pippo pippo - pluto 6 3 pippo pippo - topolino 2 1 pippo pluto - pippo 4 2 topolino topolino 5 5 topolino topolino - pluto 4 2 topolino pluto - topolino 10 5 topolino pippo - topolino 2 1 Totale 39

In wich I have:

- am: all am (am1 or am2)

- am team: am1 + am2

- sales: sales of 'am team'

- sales by am: if 'am team' is composed of 1 am -> sales, if 'am team' is composed of 2 am -> sales/2

how can I get this result?

Thanks

• ###### Re: Measure divided between (team of) two dimensions / columns

Here is a load script that will transform your input into the output you need:

```T1:
If( IterNo()=1, am1 , am2 ) AS am,
am1 & If( ams=2 , '-' & am2) AS [am team],
sales,
If( ams=2 , sales/2 , sales ) AS [sales by am]
WHILE
IterNo()<=ams;
am1,
am2,
sales,
If( am2 = '' , 1 , 2 ) AS ams
INLINE [
am1,am2,sales
pluto,,7
pluto,pippo,4
pluto,topolino,10
pippo,,1
pippo,pluto,6
pippo,topolino,2
topolino,,5
topolino,pluto,4
];
```

• ###### Re: Measure divided between (team of) two dimensions / columns

Hi Francesco,

you can try with this...

bye

Script

*,

SubField([am team],' - ') as am1

;

am1& if(Len(am2)>0, ' - ' & am2) as [am team],

sales,

if(Len(am2)>0,sales/2,sales) as [sales by am]

;

am1, am2, sales

pluto, , 7

pluto, pippo, 4

pluto, topolino, 10

pippo, , 1

pippo, pluto, 6

pippo, topolino, 2

topolino, , 5

topolino, pluto, 4

];