topic Simple accumulation problem in line chart in QlikView

Simple accumulation problem in line chart

Wed, 23 Sep 2015 05:43:50 GMT

Hi!

I'm writting because I have a simple problem which couldn't solve yet.

I have the following table:

‌

As you can see, the fields are year, month, day, date (which summarizes the former 3 fields in date format) and sales.

There is a row for each day from 1 june 2015 to 31 august 2015, and in each row the value of Sales field is 10.

Mi goal is to show a line chart where when filtering by year and month, the chart would show the days of that month in the X axis and for each date the sales of the last 60 days until that day in the Y axis (for example, in 20 august in X axis it should show 600 sales because 600 is the number of sales from 20 of june to 20 of august).

Because there are 10 sales each day, the chart should show an horizontal line with 600 as Y axis value, however, the result I get is quite different:

The expression I'm using to get the sales from last 60 days is the following (note that I'm using Sum(Sales) with 60 steps of accumulation):

Obviously, the result I get (a line from 0 to 300 sales) is not the expected result (an horizontal line fixed at 600 sales).

What I need is a solution, no matter if using SET ANALYSIS, ACCUMULATION or whatever in order to reach my goal.

If there is any doubt, please do not hessitate in asking for further information.

I've attached the files Sales.qvw and Sales.xlsx (both files should be in the same folder in order to load the data from the xlsx to the qvw).

Thanks in advance!
Matias

Re: Accumulation in chart beyond boundaries

ecolomer — Wed, 23 Sep 2015 06:39:37 GMT

You need use SET ANALISYS, see this documnets:

Re: Accumulation in chart beyond boundaries

nagarjuna_kotha — Wed, 23 Sep 2015 06:40:00 GMT

Hi,

Can u share some sample data for better and quick result.

Re: Accumulation in chart beyond boundaries

Tue, 29 Sep 2015 04:08:52 GMT

Thanks for the answers. Enrique, I know what set analysis is. What I do not know is how to solve my problem in

this case using it. I've attached a sample QVW. The sample has sales from 1 of june to 31 of

august, and in each day there are 10 sales (so, the expected result is that the sales accumulation from 60 days

on each day are 600 sales -a constant horizontal line-).

Re: Accumulation in chart beyond boundaries

Anonymous — Fri, 24 Jun 2016 14:45:01 GMT

So here is what you do. I changed the sales numbers a little bit so that the "Sales per day" line on the chart wasn't hidden.

Create a variable...let's call it v60Day. It's value "=Date(Max(Date)-60)" (don't forget the equals sign, that's important). This will give you the date 60 days before the latest date in your selection.
Here is your expression for "Sales last 60 days": Aggr(Sum({1<Date={">$(=v60Day)"}>} TOTAL Sales),Date)

Here is the result...the straight chart at the bottom shows what the the Aggr statement above really does.