Rolling 13 Month View Not Working As Expected

[pnn44794]

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.

[pnn44794]

The following is the last expre3ssion we tried.

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

[eduardo_sommer]

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

[pnn44794]

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.

[eduardo_sommer]

Hi

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

[pnn44794]

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.

[eduardo_sommer]

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

[pnn44794]

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.

[eduardo_sommer]

Then you can use the following expression:

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

Eduardo

[pnn44794]

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.

[pnn44794]

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.