see the attached approach
I added 2 calculations to create this
the calcualtions are
If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution. Thank you.
Currently your data is structured like this:
Case # Open Date Close Date
1 1/1/01 1/1/02
2 2/1/01 2/1/02
3 3/1/01 3/1/02
But you need your data formatted like this:
Case # Date Type
1 1/1/01 Open
1 1/1/02 Close
2 2/1/01 Open
2 2/1/02 Close
3 3/1/01 Open
3 3/1/02 Close
Then you can just put CNTD(Case #) to rows, Date to columns, and Type to color and Type again to Detail.
The issue is that he's got his dates in two different date fields. Notice how in his workbook there are different values for open and close, but in this solution, the values for open and close come back identical. The date fields need to be pivoted.
Both bars have same values in your attached workbook as the column only brings in "Case Open" dimension and therefore it display values for Open only
Mike, Is there a way to write the calculation and Pivot at the workbook level instead of Pivoting the entire datasource?
You can't pivot with a calculation. What kind of data source are you working with?
Instead of pivoting, I brought the same datasource twice and blended/joined them on quarter column.. It worked.