I'm trying to take a stab at this. Can you explain how the columns 0,1,2 are calculated? You mention that it's an offset, but how?
Thanks for having a look. The offset is calculated by the months difference between 2 dates (the one you see in the screenshot and another date in my dataset)
This one is pretty tricky, I'm not having much success so far. Do you mind posting a packaged workbook so myself and other folks can take a look?
I was thinking this might be easier to do with custom SQL....not sure what your comfort level is with writing those types of queries.
2 of 2 people found this helpful
This is done using a healthy dose of table calculations and custom grand totals, if you want (a lot of) background reading check out the links at http://drawingwithnumbers.artisart.org/want-to-learn-table-calculations and http://drawingwithnumbers.artisart.org/customizing-grand-totals-part-1 and http://drawingwithnumbers.artisart.org/customizing-grand-totals-part-2.
For the data I used Superstore and used a calc to change the Categories into the Offset dimension, and filtered out some data to make it look like the screenshot above.
Then here's roughly how I built out the view and the calcs. I created two different solutions, one with a single calc and one with two calcs and this latter one is the one in the screenshot above where we can get different number formats for the detail rows and the grand total, so I'll cover that one:
1) Created custom dates for the month & year dimensions and used those on Rows.
2) Created duplicates of the two custom date dimensions and put the copy dimensions onto the Level of Detail Shelf. This is using the duplicated dimension technique from the 2nd grand totals post. This is necessary to give us enough detail inside the grand total to get the calcs to work right.
3) Created a First Offset Sales table calculation with the formula LOOKUP(SUM([Sales]),FIRST()). With a Compute Using on Offset it returns the values of the Offset 0's sales to each new offset. Since it only does the carry forward for the marks in the view it *does not* carry forward Offset 0/Dec 2016 sales into Offset 1/Dec 2016.
4) Created a Sales for Dual table calculation with the formula IF SIZE() = 1 THEN SUM([Sales]) END that has a compute using on Year (copy) and Month (copy). With that compute using setting SIZE() only returns 1 in the detail rows, not the grand total. This has the standard number format.
5) Created a Ratio for Dual table calculation with the formula
IF SIZE() > 1 AND FIRST() = 0 THEN
WINDOW_SUM(SUM([Sales])) / WINDOW_SUM([First Offset Sales])
This also has a compute using on Year (copy) and Month (copy). With this compute using setting SIZE() will be >1 in the grand total, and the FIRST()=0 is used to return only a single result out of all the months that are in the grand total. The two WINDOW_SUM's add up the numbers to create the ratio.
The workout worksheet shows all the calcs, then I duplicated that sheet and moved pills to create the final view, the one last step was to turn off Analysis->Stack Marks to get rid of the ellipses in the Grand Total and restore the regular layout.
v10.0 workbook is attached, let me know if you have any questions!
Thanks so much. works perfectly!