4 Replies Latest reply: Nov 30, 2015 3:38 AM by Nico Manro Oberholzer

# Cumulative line chart for 7 days of data

Hi

I've done a lot of searching on this and it looks like it can be done in Sense (not as easily as it was in QV) but I have not had any luck getting this to work.

Basically, I need to display a line chart of drivers and their cumulative sum(miles) over the most recent 7 days of data.

To complicate things… it needs to be the most recent 7 days of data, not last 7 days of data. This is important because data will only be reloaded once a week, which means I can’t build the expression to say cumulative sum of today()-6

What is the expression I need either as a master calc or within a line chart object? Thank you!

Considering that today is April 2 and the last reload was March 28, one driver would show (visualize in a line, not table):

 Date Cumulative Miles Daily Miles 3/28/2015 903 25 3/27/2015 878 140 3/26/2015 738 87 3/25/2015 651 128 3/24/2015 523 201 3/23/2015 322 177 3/22/2015 145 145
• ###### Re: Cumulative line chart for 7 days of data

Hi Patrick,

In the load script, you can add a RowNo() or something like that for each value in date, so if today is day 93 since Jan 1, 93 -6 is what you are looking for, then you use this number in a set analysis expression.

Miguel

• ###### Re: Cumulative line chart for 7 days of data

Thanks Miguel,

I've added RowNo and tried this expression in a line chart (as Mike Tarallo mentions here) but still to no luck.

rangesum(above(Sum(Miles),0,7))

I know I must be missing some reference to RowNo which is why my line chart is only showing me daily miles instead of cumulative within the most recent 7 days. Thoughts?

• ###### Re: Cumulative line chart for 7 days of data

Make the chart a table, it will help to figure out what Above() actually means.

In your expression, Above() is always adding up from first row (0) to eighth row (7), that expression should be a straight line.

Instead, use something like

RangeSum(Above(Sum(Miles), NoOfRows(), 7))

Or NoOfColumns() instead of NoOfRows() if you have your chart like that.

Miguel

• ###### Re: Cumulative line chart for 7 days of data

Hi pbn,

For the last 7 Days:

Sum({<Date={‘>=\$(=Date(Max(Date)-7))<=\$(=Date(Max(Date)))’}>} [Daily Miles])

By dynamically using the Max(Date) you automatically us the MAX date in the dataset. Not Date(today()). Henceforth you would be using the last loaded date of a reload, which in this case would have been 3/28/2015.

For the accumulation try:

=RangeSum(Above(Sum({<Date={‘>=\$(=Date(Max(Date)-7))<=\$(=Date(Max(Date)))’}>} [Daily Miles]),0,RowNo(TOTAL)))

Let me know if it works?

Nico