topic Re: Accumulated (%) values in chart in QlikView

Accumulated (%) values in chart

Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

I am having issues creating a chart.

What I need to get is, this.

It shows me the differences in percentages of the accumulated sales of 2016 compared to 2015. January was a good month, 21,22% more sales than January of the previous year. February the same, we still score better than January-February of 2015 (15,20% higher). March was a really bad month, which made us -6,19% behind on the sales of January-March 2015. The worsening continued until June (the selected month).

I select the current month, let's say June 2016. The expression to get the value of June is:

=(sum({$<Month={"<= $(=num(Month))"},Year={$(=only(Year))}>} Sales) / sum({$<Month={"<= $(=num(Month))"},Year={$(=only(Year)-1)}>} Sales))-1

But this is to get just one value, in my chart I would also need values of the previous months, up until the selected month and year.

How do I get all of that in a chart?

Thanks in advance!

Re: Accumulated (%) values in chart

vinieme12 — Thu, 09 Feb 2017 11:59:21 GMT

can you post a sample app

Re: Accumulated (%) values in chart

adamdavi3s — Thu, 09 Feb 2017 13:03:29 GMT

Seems to work fine in my example?

I've used your exact formula int he chart then some of my own in the table to check it

Re: Accumulated (%) values in chart

Thu, 09 Feb 2017 13:24:46 GMT

Hi,

I see what you did, but that is not what I was looking for. You now showed me the comparison of every month, separate. I need it accumulated. You are now showing me that January, June and July were better months in 2017 than in 2016. But I need to see the total image. Was January - July 2017 also better dan January - July 2016?

This is what I should get with your values (btw you calculated 2017-2016/2017), it should be (2017-2016/2016)

Re: Accumulated (%) values in chart

adamdavi3s — Thu, 09 Feb 2017 13:32:39 GMT

AH I understand now, so are you expecting only one figure to be output?

or one figure for each month

e.g. in jan compare jan + jan

in feb compare Jan-feb + jan-feb

in march compare Jan-march + jan-march ?

This should be possible, I mean it can be done in a table for sure, a chart might just be trickier!

Re: Accumulated (%) values in chart

Thu, 09 Feb 2017 13:38:51 GMT

In January 2017, when I select January 2017, it should be a chart with only one bar (the bar that shows the difference (%) between Jan 2016 and Jan 2017).

In July 2017, when I select July 2017, it should be a chart with 7 bars. Exactly the same chart as in June 2014, but with one extra bar, the bar that shows the difference (%) between Jan-July 2016 and Jan-July 2017.

A chart would be nice if possible...

Chart in July

Chart in June

Chart in Feb