4 Replies Latest reply: Mar 22, 2012 10:26 AM by Benoit ARMBRUSTER

# Expression in row and column in a straight table

Hi all,

I have to do a table like this one :

Current QuarterLast Quarter
VariationIndicator
Revenue

%
Revenue stores

%
Revenue Internet

%

Revenue is the sum of field REVENUE, Revenue stores is the sum of Revenue where SOURCE='STORE', the variation is the difference between Quarter and Quarter-1, Indicator is an arrow (using qmem pictures).

The problem, is that I should have an expression in row and column.

My idea was to use Valuelist as dimension like this :

Dim : Valuelist('Revenue','Revenue stores','Revenue Internet') <- set in the variable vList

And then to test the valuelist for the expression in each row like this :

if(\$(vList)='Revenue',

sum({<QUARTER={\$(=MAX(QUARTER))}>}REVENUE),

if(\$(vList)='Revenue stores',

sum({<SOURCE={'STORES'},QUARTER={\$(=MAX(QUARTER))}>}REVENUE),

sum({<SOURCE={'INTERNET'},QUARTER={\$(=MAX(QUARTER))}>}REVENUE)))

For the moment, this solution is working. But it is very complicated and I think hard to maintain.

So, I am there to ask for your expertise and experience, is it a good solution ?

Would you have used another method ?

Should I do something in the script ?

Thanks,

Ben

• ###### Re: Expression in row and column in a straight table

I'd create a calculated dimension:

```=ValueLoop(1,3,1)
```

and then the expressions:

```Headline:
pick(valueloop(1,3,1),
'Revenue',
'Revenue Stores',
'Revenue Internet'
)
Current Quarter:
pick(valueloop(1,3,1),
sum({<QUARTER={\$(=MAX(QUARTER))}>}REVENUE),
sum({<SOURCE={'STORES'},QUARTER={\$(=MAX(QUARTER))}>}REVENUE),
sum({<SOURCE={'INTERNET'},QUARTER={\$(=MAX(QUARTER))}>}REVENUE)
)
```

and so on...

• ###### Re: Expression in row and column in a straight table

Thanks Thomas,

Your solution is working fine !

So, according to you, I had nothing to do in the script ?

• ###### Re: Expression in row and column in a straight table

Well, I would load the expressions (pref. from excel) in script and use parameters.

```Let vRevenue = sum({<QUARTER={\$(=MAX(QUARTER))}>}REVENUE),
Let vRevenue_split = sum({<SOURCE={\$1},QUARTER={\$(=MAX(QUARTER))}>}REVENUE)

This replaces the expressions in table:
\$(vRevenue)
\$(vRevenue_split('STORES'))
\$(vRevenue_split('INTERNET'))

```
• ###### Re: Expression in row and column in a straight table

Thanks Thomas,