Expression in row and column in a straight table

[#1]

Hi all,

I have to do a table like this one :

	Current Quarter	Last Quarter	Variation	Indicator
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

[#2]

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...

[#3]

Thanks Thomas,

Your solution is working fine !

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

[#4 · Accepted solution]

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'))

[#5]

Thanks Thomas,

Your solution is working well.

However, if someone has another idea do not hesitate to submit it. I am interested to know if everyone would have solved the problem like this.