Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

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):

   

DateCumulative MilesDaily Miles
3/28/201590325
3/27/2015878140
3/26/201573887
3/25/2015651128
3/24/2015523201
3/23/2015322177
3/22/2015145145
4 Replies

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

Not applicable

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

nico_ilog
Contributor II

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

Community Browser