Crosstable Rolling 6 Months Date Issue

[cbaqir]

I need to show rolling 6 months of data in my chart but I think I am having an issue with the Month field not being formatted as a date correctly. Anyone have any ideas?

= sum({<Month={ " >= $(= Date(AddMonths(Max(Month),-5))) <= $(=Max(Month)) " }>} Hours)

Load Script:

EPIC_HOURS_CROSSTABLE:
UNQUALIFY*;
CrossTable(Month, Hours, 😎
LOAD
[GPL Project #],
[Ticket #],
trim( subfield([Requesting Market],',')) as EPIC_MARKET,
[Project Name],
Comments as HOURS_COMMENTS,
//[Module for Hours],
subfield(SubField([Module for Hours],'/',1),',') as [Module for Hours],
[SA Assigned],
Status as HOURS_STATUS,
[1/1/2019],
[2/1/2019],
[3/1/2019],
[4/1/2019],
[5/1/2019],
[6/1/2019],
[7/1/2019],
[8/1/2019],
[9/1/2019],
[10/1/2019],
[11/1/2019],
[12/1/2019],
[1/1/2020],
[2/1/2020],
[3/1/2020],
[4/1/2020],
[5/1/2020],
[6/1/2020],
[7/1/2020],
[8/1/2020],
[9/1/2020]
FROM
[..\External_Data\Epic Acute Care Capacity Worksheet with Month.xlsx]
(ooxml, embedded labels, header is 8 lines, table is Optimization, filters(
Remove(Col, Pos(Top, 15)),
Remove(Col, Pos(Top, 14)),
Remove(Col, Pos(Top, 13)),
Remove(Col, Pos(Top, 12)),
Remove(Col, Pos(Top, 11)),
Remove(Col, Pos(Top, 10)),
Remove(Col, Pos(Top, 9))
))
Where wildmatch( [Module for Hours], '*Clin*','*Orders*');

Final:
LOAD [GPL Project #],
[Ticket #],
EPIC_MARKET,
[Project Name],
[Module for Hours],
[SA Assigned],
Hours,
date#(num#(Month),'MM/DD/YYYY') as Month
RESIDENT
EPIC_HOURS_CROSSTABLE
where today()>=Month;

Drop TABLE EPIC_HOURS_CROSSTABLE;

[Oleg_Troyansky · Accepted solution]

I see a few potential issues with the date conversion:

- The dates appear to have a format "M/D/YYYY", but the date# function is listed with the format "MM/DD/YYYY' - that could cause a problem.

- Since you are converting the string format to the date format using date#(), I don't think that you need to convert the string to the number using num#() as well - Date#() should be sufficient

- The WHERE condition compares the today() function with the field Month that is not converted yet, which might also cause a problem. I'd recommend moving that WHERE condition to a preceding load that you could add on top of the resident load.

Having said that, I can't quite explain why the Month field looks like it is properly formatted as a date - based on all of the above, it shouldn't ... 

Best,

[cbaqir]

Is there any other way I can get the chart to show only the last rolling 6 months? I am trying to produce an Nprinting report but I've never done it with a crosstable before. 

[gp_oconnor · Accepted solution]

The expression:

sum({<[Month]={">=$(= Date(AddMonths(Max(Month),-5))) <=$(=Max(Month))"}>} [Hours])

seems to give 6 rolling months as per below.