topic Re: Simple accumulation problem in line chart in QlikView

Simple accumulation problem in line chart

Tue, 29 Sep 2015 04:44:46 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

tresB — Tue, 29 Sep 2015 05:25:47 GMT

Rather take Date as dimension and exp like:

Sum({<Year,Month,Date={">$(=Date(Max(Date)-60))"}>}Sales)

PFA

Re: Accumulation in chart beyond boundaries

Tue, 29 Sep 2015 17:01:48 GMT

Tresesco thanks for the answer! But the solution you proposed did not work.

Here is the screenshot (I've attached the updated qvw with the xlsx which must be in the same folder to be loaded).

The expected result must be an horizontal line with 600 as constant value because there are 10 sales each day. The result obtained is not the expected:

Re: Simple accumulation problem in line chart

tresB — Wed, 30 Sep 2015 07:10:29 GMT

Let me first discourage you in - posting multiple threads for the same query. That actually deteriorates the community hygiene.

Back to your query: I don't see 10 sales for a day in your sample data set. And, please don't explain about what it is coming like (because, that we can check anyway from the attachment), rather explain what you exactly want the output to be like.

Re: Simple accumulation problem in line chart

Wed, 30 Sep 2015 15:24:10 GMT

Tresesco, thanks for the answer!

Sorry for posting the same problem in multiple threads but this is very urgent. My work depends on it.

There is 10 sales each day from 1 june to 31 august in the sample dataset.

I've even put a screenshot of the dataset in my last reply, look at it.

What I want that each day (X axis) in the chart shows the sum of sales of the last 60 days: 60 days * 10 sales per day = 600 sales. So, the result in august should be a straight horizontal line with 600 as Y axis value.

Re: Simple accumulation problem in line chart

Wed, 30 Sep 2015 16:06:47 GMT

is there always going to be 10 sales per day or is this just how it is now - are you wanting to using this horizontal line at 600 more as a reference line?

Re: Simple accumulation problem in line chart

Wed, 30 Sep 2015 16:54:51 GMT

I believe you need to follow along the lines of treseco - I was able to get a line at 600 by using it as a reference line

basically the chart has 2 horizontal lines - one fore daily, whcih is 10 for each day and the other for 600

The expression I used to store in a variable was =('>=' &date(num(max(Date))-60) & '<=' & max(Date))

it is going back 60 days from the max date in the data set through and including the max date in the data set

Re: Simple accumulation problem in line chart

Thu, 01 Oct 2015 03:50:23 GMT

Adam, thanks for joining Tresesco to help me.

In the real scenario, the sales will be different each day, but I prepared an extremely simplified qvw and excel of my problem to help you guys.

I've tried using the expression you proposed (=('>=' &date(num(max(Date))-60) & '<=' & max(Date))) with no success.

Would it be too much trouble if you attach the qvw with the modification in a reply?

Re: Simple accumulation problem in line chart

tresB — Thu, 01 Oct 2015 05:27:07 GMT

Like this?

Dimension: Date

Exp: Sum(total {<Year,Month,Date={">$(=Date(Max(Date)-60))"}>}Sales)

Re: Simple accumulation problem in line chart

Thu, 01 Oct 2015 11:54:29 GMT

Matias

attached is my qvw - I used the expression as a reference line