Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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'))
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...
Thanks Thomas,
Your solution is working fine !
So, according to you, I had nothing to do in the script ?
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'))
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.