Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi there,
I am having trouble creating a table showing the following:
2014 | 2015 | Δ | Δ [%] | |
---|---|---|---|---|
Turnover | 100 | 150 | 50 | 50% |
Tons | 75 | 150 | 75 | 100% |
... | ... | .... | ... | ... |
Right now I'm using a horizontal straight table to show the first three columns which works fine. I am having trouble however adding the delta values to the table because they aren't dimensions but set analysis expressions. I also tried using a second table next to this one w/o luck.
I also thought about pre-calculating all the different delta values in the load script but that's way too many dimensions I'd have to consider.
Has anyone ever done or come across this? I'm not sure this will work at all because it's mixing up dimensions and expressions.
Thanks,
-Stefan
First of all, the example you have provided is different then the question you have asked.
Kindly use below script..
tblExample:
LOAD * INLINE [
Year, Turnover, Tons
2014, 100, 75
2015, 150, 150
];
Now use below in Straight or Pivot Table
Dimension
Use Calculated Dimension
=ValueList('TurnOver','Tons')
Expressions
1)
Label
=Year(Today())-1
Definition
=
IF(ValueList('TurnOver','Tons') = 'TurnOver', SUM({<Year = {'$(=Year(Today())-1)'}>}Turnover),
IF(ValueList('TurnOver','Tons') = 'Tons', SUM({<Year = {'$(=Year(Today())-1)'}>}Tons)))
2)
Label
=Year(Today())
Definition
=
IF(ValueList('TurnOver','Tons') = 'TurnOver', SUM({<Year = {'$(=Year(Today()))'}>}Turnover),
IF(ValueList('TurnOver','Tons') = 'Tons', SUM({<Year = {'$(=Year(Today()))'}>}Tons)))
3)
Label
Diff
Definition
Column(2)-Column(1)
4)
Label
Diff%
Definition
(Column(2)-Column(1))/Column(1)
Go to Number Tab
and Select last Expression Diff%
Fixed to 2 Decimals
Tick Show in Percent (%)
First of all, the example you have provided is different then the question you have asked.
Kindly use below script..
tblExample:
LOAD * INLINE [
Year, Turnover, Tons
2014, 100, 75
2015, 150, 150
];
Now use below in Straight or Pivot Table
Dimension
Use Calculated Dimension
=ValueList('TurnOver','Tons')
Expressions
1)
Label
=Year(Today())-1
Definition
=
IF(ValueList('TurnOver','Tons') = 'TurnOver', SUM({<Year = {'$(=Year(Today())-1)'}>}Turnover),
IF(ValueList('TurnOver','Tons') = 'Tons', SUM({<Year = {'$(=Year(Today())-1)'}>}Tons)))
2)
Label
=Year(Today())
Definition
=
IF(ValueList('TurnOver','Tons') = 'TurnOver', SUM({<Year = {'$(=Year(Today()))'}>}Turnover),
IF(ValueList('TurnOver','Tons') = 'Tons', SUM({<Year = {'$(=Year(Today()))'}>}Tons)))
3)
Label
Diff
Definition
Column(2)-Column(1)
4)
Label
Diff%
Definition
(Column(2)-Column(1))/Column(1)
Go to Number Tab
and Select last Expression Diff%
Fixed to 2 Decimals
Tick Show in Percent (%)
Thank you very much! Exactly what I was looking for.