topic Re: Accumulated chart in QlikView

Accumulated chart

PaulaRibeiro — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have some difficulties for calculating the accumulated sum. I have a line chart where I calculates the sum of the values since the beginning of the year.

I use the function RangeSum() and Above() and the line of values when I don't have any year selected is correct.

My issue is when I select one year, the values doesn't accumulate.

Any ideia to resolve that?

Best Regards

Re: Accumulated chart

sunny_talwar — Fri, 08 Jun 2018 16:30:39 GMT

Assuming this is your expression

RangeSum(Above(Sum(Measure), 0, RowNo()))

try to change it to this

RangeSum(Above(Sum({<Year>}Measure), 0, RowNo())) * Avg(1)

Re: Accumulated chart

sergio0592 — Fri, 08 Jun 2018 16:58:05 GMT

Sunny can you explain why you use * Avg(1)?

Thanks

Re: Accumulated chart

sunny_talwar — Fri, 08 Jun 2018 17:01:33 GMT

Year in the main expression will ignore selection in Year field... which would means that if the user selected 2014, they will continue to see all the years. In order to see the accumulated value only for 2014, Avg(1) is used. Avg(1) will equal 1 for selected year and 0 otherwise

Re: Accumulated chart

sergio0592 — Fri, 08 Jun 2018 17:11:41 GMT

Ok, i understand. Foxy trick! Thanks.

Re: Accumulated chart

PaulaRibeiro — Wed, 13 Jun 2018 14:00:29 GMT

It worked for the values, but when I select the year, the x-axis dimension year does not fit, show me every year. As for the row, the selected year appears and the remaining area is empty for the other years.

Re: Accumulated chart

sunny_talwar — Wed, 13 Jun 2018 15:57:02 GMT

Would you be able to share an image of what you are seeing?

Re: Accumulated chart

PaulaRibeiro — Fri, 22 Jun 2018 09:51:14 GMT

I have the expression

RangeSum(Above(Sum({<YearMonth} Measure1),0,RowNo()))

/ RangeSum(Above(Sum({<YearMonth} Measure2),0,RowNo()))*avg(1) and values doesn't acummulated.

If i have

RangeSum(Above(Sum({1<YearMonth} Measure),0,RowNo()))

/RangeSum(Above(Sum({1<YearMonth} Measure),0,RowNo()))*avg(1) the values are correct and the line too, for the year selected, but the graph show me every years.

Re: Accumulated chart

sunny_talwar — Fri, 22 Jun 2018 16:04:59 GMT

Sample or an image?

Re: Accumulated chart

PaulaRibeiro — Tue, 26 Jun 2018 11:26:34 GMT

Image. Basically, i select Year 2018 and the dimension Year of the graph show me every years, but in the graph where i have year 2017 i don't have line

Re: Accumulated chart

sunny_talwar — Tue, 26 Jun 2018 16:35:28 GMT

Where is the image?

Re: Accumulated chart

Masi_Sahargahi — Wed, 27 Jun 2018 09:53:23 GMT

HI ,

I have attached a document using cumulative sum,I hope it helps.

Re: Accumulated chart

PaulaRibeiro — Fri, 29 Jun 2018 11:44:42 GMT

I have one year selected

Re: Accumulated chart

sunny_talwar — Fri, 29 Jun 2018 11:52:59 GMT

Can you make sure that you have checked 'Suppress Zero Values' on the presentation tab of the chart properties?

Re: Accumulated chart

PaulaRibeiro — Fri, 29 Jun 2018 12:11:12 GMT

That's what was missing. Thank you for the help!

Re: Accumulated chart

sunny_talwar — Fri, 29 Jun 2018 12:12:21 GMT

No problem. Please close the thread down by marking correct and helpful responses.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny