topic Re: RangeSum does not start at zero in QlikView

RangeSum does not start at zero

Anonymous — Fri, 22 May 2015 16:39:16 GMT

Hello --

I have the following chart to accumulate budgetary/actual/forecasted sales data. As you can see in the chart, the accumulation does not start at 0. This is a very specific thing that the users are demanding. How do I make this start at 0??

This is my expression:

(RangeSum(Above(Sum({<GL_REVENUE_QUARTER=p(QUARTER),GL_REVENUE_YEAR=p(YEAR),GL_REVENUE_MONTH=p(MONTH),GL_VALUE_STREAM=p(VALUESTREAM)>} GL_BUDGET),0,RowNo())))

I am using a date island table to filter multiple graphs dates using a single filter.

I have tried unchecking supress zero values, etc. It makes no difference.

Re: RangeSum does not start at zero

Clever_Anjos — Fri, 22 May 2015 16:45:45 GMT

Would you mind sharing a sample of your application?

Preparing examples for Upload - Reduction and Data Scrambling

Re: RangeSum does not start at zero

Clever_Anjos — Fri, 22 May 2015 16:48:00 GMT

Are you sure that budget at january is 0?

Re: RangeSum does not start at zero

Anonymous — Fri, 22 May 2015 16:49:12 GMT

Hi Clever --

No, there is actual budgetary data for January - but they want the chart line to start at zero.

Thanks!

Re: RangeSum does not start at zero

Anonymous — Fri, 22 May 2015 17:28:30 GMT

Attached is a scrambled sample of the particular section of the app.

Re: RangeSum does not start at zero

Clever_Anjos — Fri, 22 May 2015 17:38:01 GMT

Try this, lightly different

(RangeSum(Above(Sum({<GL_REVENUE_QUARTER=p(QUARTER),GL_REVENUE_YEAR=p(YEAR),GL_REVENUE_MONTH=p(MONTH),GL_VALUE_STREAM=p(VALUESTREAM)>} GL_BUDGET),1,RowNo())))

Re: RangeSum does not start at zero

Anonymous — Fri, 22 May 2015 17:40:16 GMT

Hi Clever --

Unfortunately, this did not do it. It moved the line to the right:

Re: RangeSum does not start at zero

Clever_Anjos — Fri, 22 May 2015 17:43:36 GMT

Because, Jan = 0, right?

Uncheck "Supress Zero - Values" into Presentation tab

Re: RangeSum does not start at zero

Anonymous — Fri, 22 May 2015 17:47:00 GMT

Hi Clever --

This worked but now the accumulation does not total to the true total. How can I get around that?

Thanks!

Re: RangeSum does not start at zero

Clever_Anjos — Fri, 22 May 2015 17:57:52 GMT

Well, what´s the 'true total'?

As I can see you have BUDGET for each quarter, but wants to see for each month.

What values you want to see on each month?

Re: RangeSum does not start at zero

Anonymous — Fri, 22 May 2015 18:08:18 GMT

The sum of all rows should be the absolute total.

So, each month should total for that month's budget but the top right end of the line should end up being the sum of all rows. Make sense?

Thanks!

Re: RangeSum does not start at zero

Clever_Anjos — Fri, 22 May 2015 18:10:34 GMT

It´s ok, I get it, but what´s the 'budget of January'? 8006080? 8006080/3?

And Feb? 8006080/3 + 8006080/3?

Re: RangeSum does not start at zero

Anonymous — Fri, 22 May 2015 18:16:08 GMT

The budget for each month is actually just the sum of all rows that have that month.

So, in the data table these rows all sum to the total budget for January:

Re: RangeSum does not start at zero

Clever_Anjos — Fri, 22 May 2015 18:18:29 GMT

I understand, I´ve done without other dimenions but month.

If Jan´s budget is the sum() of values >0, how could be = 0?

Re: RangeSum does not start at zero

Anonymous — Fri, 22 May 2015 18:21:12 GMT

Haha - I had the same "discussion" with the users who are demanding this.

They realize that January has a value - but basically they want the line to extend down to 0 to start.

So, it'll start at the bottom left and then the first dimension label for January should have exactly what it has now.

Very silly I know - but they're very adamant that they need it to look this way to match historical reports we're converting into Qlik.

Thanks!

Re: RangeSum does not start at zero

Clever_Anjos — Fri, 22 May 2015 18:21:20 GMT

I understand, I´ve done without other dimenions but month.

If Jan´s budget is the sum() of values >0, how could be = 0?

Re: RangeSum does not start at zero

Anonymous — Fri, 22 May 2015 18:40:35 GMT

There's no way to force the line to start at a zero point and extend from there?

Re: RangeSum does not start at zero

Clever_Anjos — Fri, 22 May 2015 18:53:26 GMT

I don´t think so, because you want to "start on zero" having a "non-zero value"

Re: RangeSum does not start at zero

Anonymous — Fri, 22 May 2015 18:55:05 GMT

Could I somehow inject a row of bogus 0 data in the load script?

Thanks!

Re: RangeSum does not start at zero

Clever_Anjos — Fri, 22 May 2015 19:00:01 GMT

Let me test an idea, posting in a few minutes