
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Cumulative values trend line chart over time duration
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.
- « Previous Replies
-
- 1
- 2
- Next Replies »


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
KC

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
KC

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
KC

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
Yes, simply add your additional fields under group by:
from <>
Group by
Date,
Country;
Br,
KC
KC

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
try below expression
=sum(aggr(RangeSum(Above(total Sum(Product), 0, RowNo())),(Month,(NUMERIC,ASCENDING))))*avg(1)

- « Previous Replies
-
- 1
- 2
- Next Replies »