Hello, this image is what I need, and each month will need to append columns. iin the attached YXMD, I got the results except for the STATUS column AND I will need to manually add calculations for each new month and change the sort order. Anyone have a better idea to offer?
Solved! Go to Solution.
@LincolnMike This should do the trick. You may need to adjust the Compare field depending on how you want to handle nulls. You may also want to add a different Date field to crosstab so that the months are ordered chronologically. Otherwise, this seems to accomplish the task. Let me know if you have any questions.
Hi John, thanks for the quick response. there is a problem in the formula If [accept] = Y then 1...there are counts of 2 in some of the Y response. This makes the output a little off.
Good catch. Replace 1 with [count] in the formula and you should be good.
IF [accept] = 'Y' THEN [count] ELSE 0 ENDIF
I also noticed that what you're calling YTD is really just a total of the dataset (2017 & 2018) so I had done the same. If you intend that to be YTD as of run data you will need to incorporate a calculation that partitions year to create YTD values.
I came to the same conclusion on [count] just a moment ago. Thanks for your hard work on this.
Is there a way to attach a field name mapping of some sort to ensure the columns are arranged in the correct order?
Like a TextInput tool that just contains the headers and no data?
Well...this map seemed to work to for field order, at least to appease the people who want this particular layout order:
I created a dummy Text input file with headers and no data, attached a Sample tool to not look at any records, ensured the mapped field names match the workflow, put a union on it, and the data matched up. Good enough for this month, and it buys me another 30 days to work on another solution. I still have to work on the Status, and inserting a dash instead of nulls.