Good morning
I am having frustrating problems with getting my pivot chart to display the calculated % field instead of the actual numbers on which the % is based. I.E The chart continues to display only the number of cases and not the percentage.
In my query I have to calculate processing time of 'cases' and then simply calculate how many were processed > 13 weeks or <= 13 weeks according to various criteria. I would then like a line graph displaying these as %'s of the total processed.
My pivot table is fine (pic attached) - i created a calculated total using the %show as (percentage of row total) button. However when viewing as a pivot chart the data is based on the normal view of the actual numbers involved not the %'s shown in the table.
am baffled - any help would be most appreciated!
SELECT GATEWAY_DC_RECORDS.LTGDCCASE, GATEWAY_DC_RECORDS.AUTH_CODE, GATEWAY_DC_RECORDS.APP_TYPE, GATEWAY_DC_RECORDS.DATE_VALID, Diff2Dates("YMD",[DATE_VALID],[DATE_DEC_ISSUED],True) AS [Years Months Days], Format([DATE_DEC_ISSUED],"mmmm") AS [Month], Year([DATE_VALID]) AS [TheYear Valid], DatePart("q",[DATE_VALID]) AS [TheQuarter Valid], DatePart("q",[DATE_VALID],1,3) AS tiddles, DateFinancial([DATE_VALID]) AS [Financial Valid], DateFinancial([DATE_DEC_ISSUED]) AS [Financial Decided], GATEWAY_DC_MONITOR.LTGDC_AREA, GATEWAY_DC_RECORDS.PS2_CAT, GATEWAY_DC_RECORDS.APP_TYPE_ODPM, GATEWAY_DC_RECORDS.DECISION, Diff2Dates("D",[DATE_VALID],[DATE_DEC_ISSUED],True) AS DEcDays, DateDiff("d",[date_valid],[date_dec_issued]) AS decdaysno, IIf((([DATE_PR_TARGET]<[DATE_DEC_ISSUED]) Or ([DECDAYSNO]>91 And ([DATE_PR_TARGET] Is Null))),"Over 13 Weeks","Under 13 Weeks") AS PerformanceGroup, GATEWAY_DC_RECORDS.DATE_PR_TARGET
FROM GATEWAY_DC_RECORDS LEFT JOIN GATEWAY_DC_MONITOR ON GATEWAY_DC_RECORDS.KEYX = GATEWAY_DC_MONITOR.KEYX
WHERE (((GATEWAY_DC_RECORDS.DATE_VALID) Is Not Null) AND ((GATEWAY_DC_RECORDS.PS2_CAT) Is Not Null) AND ((GATEWAY_DC_RECORDS.APP_TYPE_ODPM)="1" Or (GATEWAY_DC_RECORDS.APP_TYPE_ODPM)="2" Or (GATEWAY_DC_RECORDS.APP_TYPE_ODPM)="3" Or (GATEWAY_DC_RECORDS.APP_TYPE_ODPM)="4" Or (GATEWAY_DC_RECORDS.APP_TYPE_ODPM)="5") AND ((GATEWAY_DC_RECORDS.DECISION) Not Like "*ithdrawn*" And (GATEWAY_DC_RECORDS.DECISION) Not Like "*ppeal*") AND ((GATEWAY_DC_RECORDS.LTGDC_TYPE)="APP") AND ((GATEWAY_DC_RECORDS.DATE_DEC_ISSUED) Is Not Null))
ORDER BY GATEWAY_DC_RECORDS.LTGDCCASE, GATEWAY_DC_RECORDS.AUTH_CODE, DateDiff("d",[date_valid],[date_dec_issued]) DESC;