36 Replies Latest reply: Nov 9, 2018 7:18 AM by Sunny Talwar

# Cumulative growth month on month in Bar Chart

Hello Everyone!

I am looking to create a bar chart showing month on month growth rates. I would like to see cumulative growth month on month along with a trend line so that the period where sales went down could be easily extracted, for the respective product. Please let me know how I can achieve this?

Best Regards,

Rony

• ###### Re: Cumulative growth month on month in Bar Chart

Hi Ron,

You can try the

RangeSum(Above(Sum(Growth),0,12)) to get cumulative growth for the last 12 months for instance.

• ###### Re: Cumulative growth month on month in Bar Chart

Hi Niclas,

How to get this as cumulative growth percent along with a trend line? Also, I have data from Jan-2014 to July-2017. That takes the total no. of months to 55. Please explain this part too.

Rony

• ###### Re: Cumulative growth month on month in Bar Chart

Hi Guys,

Any suggestions on this. I want to get a cumulative growth percentage, month on month. Please advise.

Rony

• ###### Re: Cumulative growth month on month in Bar Chart

Hi,

You add the calculation of percentage as the expression that is now noted as Sum(Growth). And for accumulated indefinitely change the 0,12 to 0,999 which will calculate for 999 months which should be plenty.

How Above() works you can see here:

https://help.qlik.com/en-US/qlikview/12.1/Subsystems/Client/Content/ChartFunctions/InterRecordFunctions/above.htm

As for trendline, why not use the built-in one that you can just add by check-mark?

• ###### Re: Cumulative growth month on month in Bar Chart

Niclas,

Could you give me the exact expression to change it to percentage. I am using Qlik Sense and dont see an option to add trendline.

• ###### Re: Cumulative growth month on month in Bar Chart

Niclas,

Any response on this?

Rony

• ###### Re: Cumulative growth month on month in Bar Chart

Sunny,

Below image shows my data structure. I want to see month on month cumulative growth as a percentage. How do I achieve this? I have data from Jan 2014 - July 2018.

• ###### Re: Cumulative growth month on month in Bar Chart

So, feb-17 will have a bar for 20/10-1 = 100% and then Mar-17 will have a bar with 30/20 - 1 = 50% + 100% = 150%?

• ###### Re: Cumulative growth month on month in Bar Chart

Hi Sunny,

My explanation was incorrect. What I am looking for is 2 things.

1) In a bar chart, I want month on month growth rate. That would be Jan 2018 over Jan 2017.

2) As a trend line in the same chart I want Year to Month Values growth. For example: Jan+Feb 2018 over Jan + Feb 2017, and so on. Below is an image with sample data in excel. Hope my explanation is clear.

• ###### Re: Cumulative growth month on month in Bar Chart

Doesn't look right to me. For example 2017 Jan is 10, Feb is 20 and Mar is 30.... but YTM17 Jan is 10, Feb is 30 and Mar is 30? Why not 60? I don't think you are taking enough time to prepare a sample. If you want good answers, please spend good time on preparing a sample.

• ###### Re: Cumulative growth month on month in Bar Chart

I am sorry about that. I have attached a new sample image.

• ###### Re: Cumulative growth month on month in Bar Chart

Correctly calculated sample data below.

• ###### Re: Cumulative growth month on month in Bar Chart

Something like this should work

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

/

Aggr(Above(RangeSum(Above(Sum(Measure), 0, Month)), 12), MonthYearField)

• ###### Re: Cumulative growth month on month in Bar Chart

Sunny,

Could you explain the expression? What is supposed to come in "Month" and "MonthYearfield"?

• ###### Re: Cumulative growth month on month in Bar Chart

Month is a field which is your dimension... J, F, M, A... etc. created in the script like this Month(DateField) as Month.

MonthYear is a field which will include month and Year information and can be created in the script like this Date(MonthStart(DateField), 'MMM-YYYY') as MonthYear

RangeSum(Above(Sum(Measure), 0, Month)) is accumulating current year

Aggr(Above(RangeSum(Above(Sum(Measure), 0, Month)), 12), MonthYearField) is accumulating the last year

Does this help?

• ###### Re: Cumulative growth month on month in Bar Chart

I have not created a cumulative field in my actual data. Do I need to create that. I apologize I am unable to understand how this formula would work.

• ###### Re: Cumulative growth month on month in Bar Chart

Hi Sunny,

I did understand. Let me try this and get back to you. Thanks a lot Sunny.

Rony

• ###### Re: Cumulative growth month on month in Bar Chart

Sunny,

The formula compares Jan 2017 against Jan 2016 and gives the right growth %. However, when I select months in the filter pane in order to see year to month growth rates, it does not come up properly. What I am looking for here is when I select Jan, Feb, March in the filter pane, the graph should show growth of  Jan 16+Feb 16+Mar 16 over Jan 15+Feb 15+ Mar 15 and so on, similarly for the other years too. Also, as I want to see this part as a trend line. As such, in order to see this part of the graph, we will need to add another expression as another measure. Am I making sense? Please guide.

• ###### Re: Cumulative growth month on month in Bar Chart

Probably help to share a sample.

• ###### Re: Cumulative growth month on month in Bar Chart

Sunny,

As per the attached image, 2017 field, 2018 field and the % next to it is coming properly. Now, YTM 17 and YTM 18 is an accumulation of 2017 field and 2018 field. The percentages next to these accumulations are not showing up. This is the issue I am facing here. I do not have any other sample apart from this. All I can do is explain. Thanks for your help.

• ###### Re: Cumulative growth month on month in Bar Chart

Don't you have an app where you are trying this? or if this is in Excel how are you loading this data? what is the script? You will have to share more than this my friend.

• ###### Re: Cumulative growth month on month in Bar Chart

Hi Sunny,

I understand what you are saying. Let me figure out a way and get back to you in this. Thanks for your help.

Regards,

ROny

• ###### Re: Cumulative growth month on month in Bar Chart

Hi Sunny,

I am attaching a dummy excel and qvf file where I have been trying to achieve the cumulative growth rates. In sheet 2 of the excel file I have month wise data for 2017 and 2018. What I want to see is "Growth 18 vs 17" (Column H) as bars in the chart and "Cumulative Growth 18 vs 17" (Column L) as a trend line, in the same chart, as  you can see in the excel file. In the attached qvf I have prepared a bar chart using the expression you sent me on Friday. Could you please check on this and let me know how I can achieve the desired result and also if there is any additional information you require.

Best Regards,

Rony

• ###### Re: Cumulative growth month on month in Bar Chart

Below is an updated image.

• ###### Re: Cumulative growth month on month in Bar Chart

Here you are, try this

Sum({<[YearMonth.autoCalendar.Year] = {"\$(=Year(Max([YearMonth.autoCalendar.Year])))"}>}Aggr(RangeSum(Above(Sum(Volume), 0, [YearMonth.autoCalendar.Month])), YearMonth))/

Sum({<[YearMonth.autoCalendar.Year] = {"\$(=Year(Max([YearMonth.autoCalendar.Year]))-1)"}>}Aggr(RangeSum(Above(Sum(Volume), 0, [YearMonth.autoCalendar.Month])), YearMonth)) - 1

• ###### Re: Cumulative growth month on month in Bar Chart

Hi Sunny,

Thank you for your response. I just checked the qvf you attached. The percentages are coming correctly when I select the years 2015 and 2016 in the year filter. However, it doesn't seem to be correct for 2017 over 2016. The % that show up for 2017 over 2016 is the same as when no year selections are made. Also, the first part of my question (trendline) where you gave the below formula is not showing up the right percentages.

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

/

Aggr(Above(RangeSum(Above(Sum(Measure), 0, Month)), 12), MonthYearField)

Could you let me know what is going wrong here? A YearMonth dimension in a continuous combo chart is what I am looking for.

Regards,

Rony

• ###### Re: Cumulative growth month on month in Bar Chart

The percentages are the same because when nothing is selected, it is comparing 2017 to 2016. Is that not what you want? Also, what do you mean when you say first part of your question? What trendline?

• ###### Re: Cumulative growth month on month in Bar Chart

Hi Sunny,

When nothing is selected, comparison between 2017 over 2016 is fine. However, this percentage (2017 over 2016) is not being calculated correctly, but 2016 over 2015 is being calculated correctly. By first part of my question I meant achieving "Growth 18 vs 17" (Column H)" in Sheet 2 of the attached excel sheet, for which you had given me the below expression as solution. However, this solution is not working right. This was to get individual month on month growth rates i.e, Jan-17 over Jan-16, Feb-17 over Feb-16 and so on. Please ignore the trend line part. I apologize if I am confusing you.

RangeSum(Above(Sum(Measure), 0, Month))/Aggr(Above(RangeSum(Above(Sum(Measure), 0, Month)), 12), MonthYearField)

Regards,

Rony

• ###### Re: Cumulative growth month on month in Bar Chart

Ron my friend, are you trying to make a fool of me? The Excel file you attached earlier had two rows for each month in 2017 brother... and then you changed your data... how are things going to match if you change your data just in the Excel and not in the app... try reloading the app with the new data my friend and see if the number match or not

• ###### Re: Cumulative growth month on month in Bar Chart

New expression

Sum({<[YearMonth.autoCalendar.Year] = {"\$(=Year(Max([YearMonth.autoCalendar.Year])))"}>}Aggr(RangeSum(Above(Sum({<[YearMonth.autoCalendar.Year]>}Volume), 0, Only({1}[YearMonth.autoCalendar.Month]))), YearMonth))/

Sum({<[YearMonth.autoCalendar.Year] = {"\$(=Year(Max([YearMonth.autoCalendar.Year]))-1)"}>}Aggr(RangeSum(Above(Sum({<[YearMonth.autoCalendar.Year]>}Volume), 0, Only({1}[YearMonth.autoCalendar.Month]))), YearMonth)) - 1

• ###### Re: Cumulative growth month on month in Bar Chart

Hi Sunny,

I apologize. It was an honest mistake. Let me attach a final qvf and excel with correct data. In sheet 2 of attached excel you will find "Growth 18 vs 17 (Column H)" and "Cumulative Growth 18 vs 17 (Column L)." Please help me achieve this in 2 charts respectively as I am still not able to get it right. Trying to fool you will be my loss. Thanks again.

Rony

• ###### Re: Cumulative growth month on month in Bar Chart

Would you agree that you already have "Cumulative Growth 18 vs 17"? For "Growth 18 vs 17", try this expression

Sum({<[YearMonth.autoCalendar.Year] = {"\$(=Year(Max([YearMonth.autoCalendar.Year])))"}>}Volume)/

Sum({<[YearMonth.autoCalendar.Year] = {"\$(=Year(Max([YearMonth.autoCalendar.Year]))-1)"}>}Volume) - 1

We see -100% for Sep, Oct, Nov, and Dec for obvious reasons that there is no data available for Sep, Oct, Nov, and Dec of 2018 which is why the percentage is -100%. If you can to get ride of this, you can add an if statement to remove it... If(Sum(Volume) <> 0, then do something, otherwise do nothing)

• ###### Re: Cumulative growth month on month in Bar Chart

yes I agree. Let me try this and get back to you

• ###### Re: Cumulative growth month on month in Bar Chart

Hi Sunny,

Thanks a lot for your help and for taking the time to resolve this issue . The expressions you sent me are working perfectly. There is one last question I have and that is, if I take "MonthYear" instead of "Month" as dimension, what kind of changes will have to be made to the expression, both cumulative and month on month.

Best Regards,

Rony

• ###### Re: Cumulative growth month on month in Bar Chart

So, let me ask you this.... if you have MonthYear... would you still want to see 1 year on the chart?

• ###### Re: Cumulative growth month on month in Bar Chart

Hi Sunny,

I want to see all the years continuously. As of now I have data from Jan-2014 to July-2018.

Rony

• ###### Re: Cumulative growth month on month in Bar Chart

Cumulative Growth or just month on month growth?

• ###### Re: Cumulative growth month on month in Bar Chart

I would like to see both Sunny.

Regards,

Rony

• ###### Re: Cumulative growth month on month in Bar Chart

It has been few days since we last worked on this... would you be able to provide the required output... what would the numbers be when you see them with MonthYear as your dimension for both

• ###### Re: Cumulative growth month on month in Bar Chart

Yes. I was on leave for the past few days. Numbers would be the same, in percentages. One chart would be month on month and the other cumulative. I just want the dimension to be continuous, i.e, Jan-14 to July-18 in one shot, Rest all remains the same.

Rony

• ###### Re: Cumulative growth month on month in Bar Chart

Try these

Cumulative

RangeSum(Above(Sum({<[YearMonth.autoCalendar.Year]>}Volume), 0, Only({1}[YearMonth.autoCalendar.Month])))/

Above(RangeSum(Above(Sum({<[YearMonth.autoCalendar.Year]>}Volume), 0, Only({1}[YearMonth.autoCalendar.Month]))), 12) - 1

Non cumulative

Sum({<[YearMonth.autoCalendar.Year] = {"\$(=Year(Max([YearMonth.autoCalendar.Year])))"}>}Volume)/

Above(Sum({<[YearMonth.autoCalendar.Year] = {"\$(=Year(Max([YearMonth.autoCalendar.Year]))-1)"}>}Volume), 12) - 1

• ###### Re: Cumulative growth month on month in Bar Chart

My bad, for non cumulative... try this

Sum(Volume)/Above(Sum(Volume), 12) - 1

• ###### Re: Cumulative growth month on month in Bar Chart

Thanks a lot Sunny!

Best Regards,

Rony

• ###### Re: Cumulative growth month on month in Bar Chart

No problem at all. Glad I was able to help.

Best,

Sunny