Skip to main content
Announcements
SYSTEM MAINTENANCE: Thurs., Sept. 19, 1 AM ET, Platform will be unavailable for approx. 60 minutes.
cancel
Showing results for 
Search instead for 
Did you mean: 
Ivy_Robin
Contributor
Contributor

Line Chart with rolling days and aggregation about time

Hi Everyone,
My goal is to create a line chart, which shows the rolling 7 days Values for the aggregated 3 Days sums per day.
My Data look like:

Ivy_Robin_0-1719416390980.png

and I need a chart like this:

Ivy_Robin_1-1719416498066.png

1193 ist the sum of Product A+B+C for the days 01.01.2024+02.01.2024+03.01.2024. etc.

Additional following function is needed: I the App you have to pick a date. The line chart should show the picked date and the 7 days before, not more. 

Any ideas to do this? I had a lot of trys with aggr() and Rangesum() but nothing gave me the result above.

Thanks for your help.


Labels (1)
1 Reply
marcus_sommer

It should be possible with aggr() and rangesum() + interrecord-functions like above(). But it's a quite complex task.

The main-difficulty by such approaches is to access the values from different dimension-values to the calling one. Means for example the first dimension-value won't only grab the own measure-values else also the ones from n others and between them does not exists a valid association. They are excluding each other.

This could be bypassed with an aggr() which creates a virtual table and spanning there an appropriate wide range of dimension-values. With the interrecord-functions each cell-range from this table could be addressed and accumulated with a range-function. The special difficulties are to implement the right conditions within the underlying expression as well as within the aggr() construct and also from the outside of it to size respectively synchronize this bigger table with the calling object.

Personally I would try to avoid such way and doing the essential work within the data-model by creating an appropriate The As-Of Table - Qlik Community - 1466130. In your case it might be:

AsOf:
load *, date(AsOfDate - iterno() + 1) as Date while iterno() <= 7;
load date(fieldvalue('Date', recno())) as AsOfDate autogenerate fieldvaluecount('Date');

and then using the AsOfDate as dimension within the line-chart. It's not a pre-aggregation else only an associating of one dimension-value with n others.