topic Re: Rolling 13 Month Stacked Bar Chart View in QlikView

Rolling 13 Month Stacked Bar Chart View

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

Hello:

I realize there's a few posts regarding rolling views and I've tried some of the sample code (using rangesum and couple of other examples), but none quite work for me, but that could be my fault as I'm still new to Qlikview.

What I'm looking to do is create a stacked bar chart that would be comprised of a Business, i.e. Business1, Business2, etc. on top of each other in the chart, showing 13 months of billing information. I pull the data directly from a database into Qlikview.

The fields I have are:
Business1 through to Business10 - Easy enough to get this part

CHARGE_AMOUNT - A dollar based figure I would need to sum for each month

CHARGE_MONTH in the form YYYYMM, i.e. 201609 - For the 13 month rolling view, if ran today, I'd like to see 201509 through 201609. Next month I would want to see 201510 through 201610. Etc.

I'm essentially stuck on creating the formula / expression to do this. Any help would be greatly appreciated. Thanks in advance.

Re: Rolling 13 Month Stacked Bar Chart View

sunny_talwar — Tue, 20 Sep 2016 00:49:03 GMT

May be it would be easier if you can provide some sample data with the output you expect to see from it.

Re: Rolling 13 Month Stacked Bar Chart View

ramasaisaksoft — Tue, 20 Sep 2016 08:22:24 GMT

Hi Newman,

I hope you wan the sum value of all 13 months for business wise

so x- axis is business 0, business 1...etc

Y -axis sum value ($ 1900,$2000...etc ) of 13 months

and u need to write if (month(CHARGE_MONTH )='Sep',sum(CHARGE_AMOUNT )

you will get month wise amount.

if this solution is not suitable for you kindly post your sample data and expected output.So that our folks will help to you ASAP.

Re: Rolling 13 Month Stacked Bar Chart View

pnn44794 — Tue, 20 Sep 2016 15:17:48 GMT

Hi Rama. Please see my reply to Sunny T. I hope that gives you a better idea of what I'm looking for / trying to do.

Re: Rolling 13 Month Stacked Bar Chart View

pnn44794 — Fri, 23 Sep 2016 18:53:57 GMT

Test sending.

Perry Newman

Re: Rolling 13 Month Stacked Bar Chart View

pnn44794 — Fri, 23 Sep 2016 20:27:26 GMT

Hello Sunny:

Do you need anymore information?

Re: Rolling 13 Month Stacked Bar Chart View

harishkumarg — Sat, 24 Sep 2016 18:33:03 GMT

Please find the attached ap.

Set analysis will help you to display the last 13 moths data.

Please try to build your expression similar to the one in the attached app.

Regards

Harish

Re: Rolling 13 Month Stacked Bar Chart View

sunny_talwar — Mon, 26 Sep 2016 00:19:02 GMT

All I need is few rows of data (dummy and made up data can work) and the output you expect to see from that data. I am sure the questions might not be too difficult to understand, but unfortunately I am unable to understand the requirement.

Best,

Sunny

Re: Rolling 13 Month Stacked Bar Chart View

pnn44794 — Mon, 26 Sep 2016 19:28:39 GMT

Hello Harish:

So the expression gives me, right now, 201509 through 201609, which is what I want and it calculates the charge amount correctly. I’d like to see what happens once we go into October before marking this as the correct answer. A big thank you in advance for your help. I appreciate it.

Perry

Re: Rolling 13 Month Stacked Bar Chart View

pnn44794 — Mon, 26 Sep 2016 19:30:01 GMT

Hello Sunny:

Harish may have given me the correct answer, wish I appreciate. I'm waiting to see what happens when we roll over to October.

At the same time, I appreciate you responding as well. Thank you.

Perry

Re: Rolling 13 Month Stacked Bar Chart View

harishkumarg — Wed, 28 Sep 2016 08:11:23 GMT

Hi Perry

Once we are into October you will be able to see 201510 through 201610.

Please let me know if it doesn't work.

Regards

Harish

Re: Rolling 13 Month Stacked Bar Chart View

pnn44794 — Wed, 28 Sep 2016 16:40:09 GMT

Will do. Thank you again.

Re: Rolling 13 Month Stacked Bar Chart View

harishkumarg — Fri, 30 Sep 2016 19:19:11 GMT

NP. In case if you feel its working as you expected try to mark it as correct or useful answer.

Regards

Harish

Re: Rolling 13 Month Stacked Bar Chart View

pnn44794 — Fri, 07 Oct 2016 14:37:23 GMT

Hello Harish:

I wanted to give you an update. I have reloaded the data and I still see 201509 through 201609. However, that could be because I don't have any 201610 charges yet and won't until the end of the month. I will go ahead and mark your answer as the correct one and if after loading 201610 data, I don't see 201510 through 201610, I'll let you know. Thanks again for your help.