Stacked bar chats with cumulative amount and missing values

[fabrice_lattern]

Hello !

I am trying to do a cumulative stacked bar chart with two dimensions (basically time & some geographical region). It works when I have data for each period of time and each region, but it is not working when there is no data for a given period & region.

Here is a simplified example of load script :

FACT:
LOAD * INLINE [
PERIOD, REGION, AMOUNT
1, Europe, 100
1, Asia, 120
1, America, 80
2, Europe, 110
2, Asia, 70
2, America, 150
3, Europe, 80
3, Asia, 100
3, America, 90
4, Europe, 10
4, Asia, 120
6, Europe, 100
6, America, 90
];

And here is the formula I used :

=aggr(rangesum(above(Sum(AMOUNT), 0, rowno())), REGION, PERIOD)

And the incorrect result :

Inserting dummy records to cover the gap is a bit complicated in the real context (and not elegant). Do you know if there is an other way to procede ?

Thanks in advance 🙂

[Kushal_Chawda]

@fabrice_lattern  what is expected results?

[fabrice_lattern]

Hello,

It is to avoid to have the gap (for example America on period 4 should remain at 320 even if there is no data for America on this period).