topic Re: Rolling 13 Month View Not Working As Expected in QlikView

Rolling 13 Month View Not Working As Expected

pnn44794 — Fri, 21 Oct 2016 19:48:28 GMT

Hello:

I had asked for assistance with this in an earlier thread and we thought we had it working, but it is not. I have a stacked bar chart where I would like to have a 13 month rolling view of the data. I'm using the following expression:

=Sum({<Date={">=$(=MonthStart(Today(),-12))<=$(=MonthEnd(Today()))"}>}CHARGE_AMOUNT)

When I first put this in place, it appeared to work as 201509 through 201609 was displayed. However, I've now loaded 201610 data and instead of seeing just 201510 through 201610, I'm seeing 201509 through 201610. I want 201509 to fall off.

Any ideas? As usual, any and all help will be appreciated. Thanks in advance.

Re: Rolling 13 Month View Not Working As Expected

sunny_talwar — Fri, 21 Oct 2016 20:06:22 GMT

Try to check it with just > instead of >=

=Sum({<Date={">$(=MonthStart(Today(),-12))<=$(=MonthEnd(Today()))"}>}CHARGE_AMOUNT)

Re: Rolling 13 Month View Not Working As Expected

pnn44794 — Fri, 21 Oct 2016 20:09:45 GMT

Do I need to do a reload after making the change? I would thin I would not have to. If I don't need to, I made the change, saved it, clicked Apply and then Ok. No change in the display.

Re: Rolling 13 Month View Not Working As Expected

sunny_talwar — Fri, 21 Oct 2016 20:27:01 GMT

What is your Date format?

Re: Rolling 13 Month View Not Working As Expected

pnn44794 — Fri, 21 Oct 2016 20:29:24 GMT

Date format is YYYYMM, i.e. 201610 for October 2016.

Re: Rolling 13 Month View Not Working As Expected

sunny_talwar — Fri, 21 Oct 2016 20:39:22 GMT

Can you try this:

=Sum({<Date={">=$(=Date(MonthStart(Today(),-12), 'YYYYMM'))<=$(=Date(Floor(MonthEnd(Today())), 'YYYYMM'))"}>} CHARGE_AMOUNT)

Re: Rolling 13 Month View Not Working As Expected

pnn44794 — Fri, 21 Oct 2016 20:44:41 GMT

Same result. I still see months 201509 through 201610.

Re: Rolling 13 Month View Not Working As Expected

sunny_talwar — Fri, 21 Oct 2016 20:48:04 GMT

Would you be able to share a sample? Difficult to know without seeing the issue

Re: Rolling 13 Month View Not Working As Expected

pnn44794 — Sun, 23 Oct 2016 00:01:02 GMT

Can you use what I provided in the thread?

Re: Rolling 13 Month View Not Working As Expected

sunny_talwar — Mon, 24 Oct 2016 05:37:31 GMT

Unfortunately Perry, I don't have enough information to propose any changes. I think it should it work and from the information provided, I don't see any reason why it shouldn't work.

But I sincerely hope that some one else might be able to help you better here.

Best,

Sunny

Re: Rolling 13 Month View Not Working As Expected

pnn44794 — Sun, 13 Oct 2019 20:18:42 GMT

The following is the last expre3ssion we tried.

Sum({<Date={">=$(=Date(MonthStart(Today(),-12), 'YYYYMM'))<=$(=Date(Floor(MonthEnd(Today())), 'YYYYMM'))"}>} CHARGE_AMOUNT

Re: Rolling 13 Month View Not Working As Expected

eduardo_sommer — Mon, 24 Oct 2016 19:19:33 GMT

Hi, Perry

Are these dates stored as dates? Looking at the INVENTORY_MONTH, you can find a curious YYYYMM of 201600. Maybe you must convert your data to date, before making any calculation on them.

Eduardo

Re: Rolling 13 Month View Not Working As Expected

pnn44794 — Mon, 24 Oct 2016 19:43:08 GMT

Hello Eduardo:

INVENTORY_MONTH can be ignored in this case as it’s not in play. 201600 is deliberately set to that value in the DB I pull data from and actually represents the December inventory month that gets billed in January. However, you raise a good point. CHARGE_MONTH is defined in the database as varchar. I’m guessing I need to convert it to a date format and do that conversion in my SQL code prior to loading the data into the QVD file??? If yes, would you have some SQL Server sample code? If not, I can probably find some. Thanks in advance.

Re: Rolling 13 Month View Not Working As Expected

eduardo_sommer — Mon, 24 Oct 2016 20:23:48 GMT

If it is a char with 6 caracteres in the YYYYMM format you can Translate it to a date format with Makedate(left(CHARGE_MONTH,4),right(CHARGE_MONTH,2),'01')

Eduardo

Re: Rolling 13 Month View Not Working As Expected

pnn44794 — Mon, 24 Oct 2016 21:20:21 GMT

My apologies if I’m not getting this, but are you saying I should have an expression with two lines? Like the following in the order listed?

Makedate(left(CHARGE_MONTH,4),right(CHARGE_MONTH,2),'01')

=Sum({<Date={">=$(=Date(MonthStart(Today(),-12), 'YYYYMM'))<=$(=Date(Floor(MonthEnd(Today())), 'YYYYMM'))"}>} CHARGE_AMOUNT)

What I’m trying to get to is a 13 Month rolling view. I have CHARGE_MONTH in the format of YYYYMM, but varchar data type. When I use just the Sum line above, 201509 through 201610 show up in my chart. The chart should only show 201510 through 201610. In November, it showed 201511 through 201611 and so on.

Re: Rolling 13 Month View Not Working As Expected

eduardo_sommer — Mon, 24 Oct 2016 21:58:39 GMT

No, excuse me. O forgot to tell you to use this function in the load script to transform the date from the 'YYYYMM' format to a date format, só you can use it in your set analysis expression.

Eduardo

Re: Rolling 13 Month View Not Working As Expected

pnn44794 — Tue, 25 Oct 2016 00:46:46 GMT

Ok. This is good and I think getting me closer, but the CHARGE_MONTH now shows up as MM\DD\YYYY and I need it to be YYYYMM.

Re: Rolling 13 Month View Not Working As Expected

eduardo_sommer — Tue, 25 Oct 2016 00:52:10 GMT

Then you can use the following expression:

date(Makedate(left(CHARGE_MONTH,4),right(CHARGE_MONTH,2),'01'),'YYYYMM')

Eduardo

Re: Rolling 13 Month View Not Working As Expected

pnn44794 — Tue, 25 Oct 2016 01:34:10 GMT

Thank you Eduardo. That took care of the format problem. I still need to figure out the rolling 13 month view. I’ll look at it tomorrow. It’s been a long day and it’s still not over. Got a work issue I need to deal with. Anyway, thank you again. Hopefully, I’ll figure out the 13 month rolling view or with help from the community.

Re: Rolling 13 Month View Not Working As Expected

pnn44794 — Tue, 25 Oct 2016 14:12:14 GMT

I think I need to add as CHARGE_MONTH at the end, like the following:

date(Makedate(left(CHARGE_MONTH,4),right(CHARGE_MONTH,2),'01'),'YYYYMM') as CHARGE_MONTH

Do I need to double quote CHARGE_MONTH, i.e. "CHARGE_MONTH"? That's how it was originally in the Load.