Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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
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?
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
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