Hi there,
I must be stuck in something very simple... but I cannot find my error, let alone how to solve it
I have a dimension [BU] and two measures, [Phase] and [OI]. I load them from an Excel file. In the load script I create new measures to split the OI values after the Phase value so I can plot them separately and make calculations with them separately as well, something like this:
BU | Phase | OI |
---|---|---|
Groceries | P0 | 10 |
Appliances | P0 | 30 |
Diverse | P2 | 25 |
Groceries | P1 | 12 |
Groceries | P3 | 9 |
Appliances | P1 | 5 |
in the original load script I had something like this:
LOAD
BU,
Phase,
OI
FROM [....//file.xlsx]
(ooxml,...);
but I modified it and now it looks like this:
LOAD
BU,
Phase,
OI
if(Phase='P0',OI) as OI_Phase0,
if(Phase='P1',OI) as OI_Phase1,
if(Phase='P2',OI) as OI_Phase2,
FROM [....//file.xlsx]
(ooxml,...)
I can use the measures OI_Phase0, OI_Phase1 and OI_Phase2 in charts with no issue, such measures contain the values I expect them to. They plot both perfectly in charts and in tables.
However, I created in a separate script (after the load script), a similar INLINE table like this:
[New_Table]:
LOAD * INLINE [
Region, Phase_Value
R1, OI_Phase0
R2, OI_Phase2
R3, OI_Phase1
];
If I try to plot a bar chart with Region as dimension and Phase_Value as measure, it shows only the X and Y axis, but no bars. To test if my bar chart was OK I replaced the OI_Phase0, OI_Phase1 and OI_Phase2 with fixed values, like this:
[New_Table]:
LOAD * INLINE [
Region, Phase_Value
R1, 5
R2, 10
R3, 3
];
and this is plotted perfectly!!!
I also tried this out of desperation:
[New_Table]:
LOAD * INLINE [
Region, Phase_Value
R1, sum(OI_Phase0)
R2, sum(OI_Phase2)
R3, sum(OI_Phase1)
];
But that did not work either...
I think the values of the measures (OI_Phase0, OI_Phase1 and OI_Phase2) are not being passed to the next script. I do not know how to accomplish this or if there is something I am doing that is not making it work... Maybe my assumption that once loaded any measure can be used afterwards in any script is not correct?
What can be wrong?
Thanks in advance for any help or hint!
I found out after long time trying to solve this that INLINE tables are only for fixed values...
*sigh*
Well, at least using INLINE tables is not the way to solve my issue of using loaded measures in a script...