Cumulative values trend line chart over time duration

[zela9123]

Hi All,

Please see below sample data set:

I would like to present trend line chart with cumulative values. What is more, the default view is set up for 2020. I am using below formula:

RangeSum(Above(Sum(Product), 0, RowNo()))

Generally it is working fine, but unfortunately it is showing cumulative values starting from 2020-01: 12, 2020-02: 17 etc. I want to show total cumulative values but with the YTD view (2020). So in 2020-01:70, 2020-02: 75 etc.

I look forward to hearing from you.

[jyothish8807]

Hi Zela,

Try this: In script

[Table]:

Load
if(RowNo()=1,Product,peek(NewProd) + Product) as NewProd,
Month,
Product;
LOAD * INLINE
[
Month,Product
201901,3
201902,9
201903,4
201904,10
201905,1
201906,2
201907,5
201908,9
201909,5
201910,2
201911,7
201912,9
202001,6
202002,4
202003,10
202004,8
202005,2
202006,2
202007,5
202008,10
202009,1
202010,8
202011,9
202012,9
](delimiter is ',');

Frond end:

Dimension : =if(Month >= 202001,Month)

Exp: Sum(NewProd)

Br,

KC

[zela9123]

Hi,

Thanks for your response.

The newly created column actually shows the accumulative values, but unfortunately expression: Sum(NewProd) displays incorrect calculation (instead of showing trend, it is summing the accumulative values).

In this case, NewProd should be treated more like a dimension itself - but to create a line chart, I need to add measure.

Best regards,

Zela

[jyothish8807]

Hi Zela,

I a a bit confused, can you please share your expected output ?

Normally with the logic i have mentioned for 202001 you will get 70 and 75 for 202002 as per your data.

Br,

KC

[zela9123]

Hi,

The case is that as in my sample data, I have for example 3 records for '2020-07' etc. 

If there is sample data like you provided - per each month - one record, then it is working fine. But if there is more records per each month, it is showing wrong values.

Best Regards,

Zela

[jyothish8807]

Hi Zela,

In that case try like this:

Load
if(RowNo()=1,Product,peek(NewProd) + Product) as NewProd,
Month,
Product;

Load

Month,
sum(Product)  as Product

from < Filename>

group by

Month;

Br,

KC

[zela9123]

Hi,

Now it is working, thanks !

However I still have an issue:

 I need to load also additional Column: Country, but when I am trying to do that there is an error: Invalid expression - probably because of the 'group by' ?

Best regards,

Zela

[jyothish8807]

Hi,

Yes, simply add your additional fields under group by:

from <>

Group by

Date,

Country;

Br,

KC

[zela9123]

Hi,

When I am adding Country like you mentioned, unfortunately it is showing totally wrong values 😞 

Please see attached chart without Country loaded (cumulative values working properly), and without Country.

Do you have any idea how it can be solved?

Regards,

Zela

[Kushal_Chawda]

try below expression

=sum(aggr(RangeSum(Above(total Sum(Product), 0, RowNo())),(Month,(NUMERIC,ASCENDING))))*avg(1)