Hi Diana -
Thanks for posting
I'm not sure what you mean by "until I create a remove first year field (discreet)" Could you elaborate on that?
I tried to add a Grand Total into the following:
The Remove1stYrFilter is simply a calculated field that is filtered on true for First()!=0. This removed the extra columns that appear above. However, when I try to Grand Total the worksheet, I get the following error:
When I remove the 1stYrFilter, I get the following view:
I just want the 4 columns to the right with a Grand Total but it seems to be almost impossible. I'm hoping I am just missing a step somewhere. Thanks!
Ok - I'll leave it to others to point out whether there's a better way (there usually is), but my initial thought would be to create distinct measures for each of your date periods and your derived calculations. Something like . . .
CURRENT_YR = SUM(IF [YEAR]=2016 THEN [DISCHARGES] ELSE 0 END)
PRIOR_YR = SUM(IF [YEAR]=2015 THEN [DISCHARGES] ELSE 0 END)
CHANGE = [CURRENT_YR] - [PRIOR_YR]
DIFFERENCE = [CHANGE] / [PRIOR_YR]
Put those on your columns shelf and you should be able to add your grand totals without any surprise columns showing up. I like this method because it lends itself well to certain types of viz's.
I'm guessing your date parsing logic will need to be a bit more sophisticated than I've outlined here (especially if you want the report to be smart enough to know what periods you want to compare), but I hope you get the gist.
Did Andy's recommendation work for you?
I tried this and am still not getting the results I need so am clearly doing something wrong. I'm trying again right now but am open to any additional suggestions. Thanks, Diana
It is sensitive so I am unable to forward the .twbx.
It now works but I am losing some of my newborn_groupings (Medicine disappears). When I ungroup Medicine, it reappears but is missing the items I grouped under the original Medicine category that contains multiple items (other, null, etc). If I can't figure this piece out, I may be able to "mock" up the .twbx and provide a solid example of what I am working on without compromising my original data. I'll keep you posted. Thanks, Diana