Join Issue? Chart works when filtered but not if cleared

[cbaqir]

My charts are supposed to show Available Checkbook Hours vs Actual Hours by Month, Division, Work Name or Task Type. If I filter on CHECKBOOK_MONTH to 11/1/20, the data in the top graph looks good. If I clear the filter, the data does not look right. I think it's a join issue but I don't know how to fix it.  

APP_QV_RCM_FINAL table contains the same fields but with different names to prevent multiple joins so which fields do I use in the charts?

The EPIC_CHECKBOOK table joins to the APP_QV_RCM_FINAL table on a concatenated key %CHECKBOOK_MONTH_MODULE of Month, Work Name, Division and Task Type.

For example, in the EPIC_CHECKBOOK table the field is Division but in APP_QV_RCM_FINAL it's DIVISION. 

in the EPIC_CHECKBOOK table the field is Module but in APP_QV_RCM_FINAL it's WORK_NAME.

in the EPIC_CHECKBOOK table the field is CHECKBOOK_MONTH but in APP_QV_RCM_FINAL it's TRX_DATE.

in the EPIC_CHECKBOOK table the field is TASK_TYPE_OPT but in APP_QV_RCM_FINAL it's TASK_TYPE.

[edwin]

some of the expression are aggr by checkbook month but you dont use that in your dimension.  i think you wanted it aggr by division only so remove checkbook month and try that.  the reason it works when month is selected i bec aggr by a single dimension value returns a single result.  

after doing that does the chart show expected results when filters are cleared?  if not, what is the expected value when the filters are cleared?

[edwin]

actually, if on the first chart you need total by division you dont need to AGGR it just straight sum.

[edwin]

even for the horizontal bar chart, since you have both dimensions - you dont need to AGGR them.  AGGR will only make it slower and you get the same result anyways

[edwin]

also note that some of your dates arent properly formatted, it may not join properly.

1/1/2021-Epic OPT ECSM--
01/1/2021-Epic OPT ECSM-ND-Delivery
01/1/2021-Epic OPT ECSM-NE-Delivery
01/1/2021-Epic OPT ECSM-PNW-Delivery
01/1/2021-Epic OPT ECSM-TX-Delivery
1/1/2021-Epic OPT EMFI--
01/1/2021-Epic OPT EMFI-ND-Delivery
01/1/2021-Epic OPT EMFI-NE-Delivery
01/1/2021-Epic OPT EMFI-PNW-Delivery

[edwin · Accepted solution]

this part caused it:

NoConcatenate
APP_QV_RCM_FINAL:
LOAD *,monthstart(date(TRX_DATE,'MM/D/YYYY'))&'-' .....

it should be   date(monthstart(TRX_DATE),'MM/D/YYYY')