Cumulative line chart for 7 days of data

[#1]

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

[Miguel_Angel_Baeyens]

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

[#3]

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?

[Miguel_Angel_Baeyens]

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

[nico_ilog]

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