I'm begining to think this may be a Table calculation as it has to look "accross" the columns to grab the next quarter's ship month? Does that sound right?
1 of 1 people found this helpful
I just took a look at this and my knee **** was a table calculation as well--just haven't come up with the solution yet.
I'll work on it and see what I come up with.
Personally, I find the view easier to read if I swap Rows and Columns so the Order Date is on Rows, that way I can read across for all the given Orders in Q1, I can see how many shipped in Jan, Feb, Mar, Apr of Q2. It's also possible to see in this layout that there are no orders that shipped for a given quarter later than the month after the quarter, which means that the data may not be sufficient to test edge cases. For example, what do you want to do about an order on March 31 that shipped over a month late, i.e. shipped in May, after the Q1+April bucket ends?
Do the shipping date dimensions need to be in the view? If they don't, you could do the above computation with a row-level calc that did something like [Ship Date] >= DATETRUNC('quarter',[Order Date]) and [Ship Date] <= (DATEADD('month',4,DATETRUNC('quarter',[Order Date]))-1).
Thanks Johnathan and Tracy!
To answer your question, no the Ship date dimension doesn't need to be within the view, and I just need the count/sum of the quantities that meet the order date and ship date criteria. So to my dismay, your solution Johnathan.....works..... in its simplicity. I'm not sure how I didn't come across this before, and feel a tad sad because it was right in front of me
I am always amazed at some of the different perspectives I get from all the people here in the Tableau community.
On a more curiois note. Trace (and Johnathan)..... would there be a table calc equivalent to achive a simlar result?
Or even some custom bin/set/group way?
I've made that same mistake a few times in recent weeks, so it's been more on my mind to pay attention to the possible simpler solutions!
There almost certainly is a table calc solution, it would need the month/year of ship date in the view and the quarter of order date at a minimum, then it could be a WINDOW_SUM() calc addressing on the month/year of ship date. Given that the level of detail is greater than needed in than your original output table, then some techniques like the IF FIRST()==0 hack would need to be applied to reduce the # of results so it would look good.