Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello !
I am facing an issue with some requirement.
I have to build a stacked bar chart that present some YTD figures + the figures of the selected period, following a split by a dimension (called "sourcing group"). So I created an island table like this :
ANALYSIS:
LOAD * INLINE [
ANALYSIS
YTD
PERIOD
];
and a dummy table with some data to reproduce the issue:
TEST:
LOAD * INLINE [
PO NUMBER, MONTH, SOURCING GROUP, AMOUNT
1, 1, A, 100
2, 1, B, 200
3, 2, A, 80
4, 2, B, 20
5, 3, A, 90
];
In my bar chart, I added the two dimensions ANALYSIS and SOURCING GROUP, and the following formula as an expression:
=if(ANALYSIS = 'YTD', SUM({<MONTH=>}AMOUNT), if(ANALYSIS='PERIOD', SUM(AMOUNT)))
The idea is to have:
- One column with the YTD figures, not depending on month selection
- One other column with the selected period figures
The issue is that on March, I have no data for sourcing group "B", and the YTD figures show only dimension "A" instead of showing the split.
I suppose that I have to use a calculated dimension, but I do not the expression to use.
Thanks in advance for any advice !
I solved it using the following formula in the expression :
SUM({<MONTH=>}if(ANALYSIS = 'YTD', AMOUNT))+
SUM(if(ANALYSIS = 'PERIOD', AMOUNT))
I solved it using the following formula in the expression :
SUM({<MONTH=>}if(ANALYSIS = 'YTD', AMOUNT))+
SUM(if(ANALYSIS = 'PERIOD', AMOUNT))