1 Reply Latest reply on Feb 6, 2012 8:09 AM by Eric Maher

    Need cube-like behavior with dates and summaries

    Eric Maher

      I am having trouble figuring out how to report out data given the current data structure.

      I am summarizing 2 different kinds of ‘spend’ [direct spend & total spend) across 4 different periods of time (Current Fiscal Year to Date, Prior Fiscal Year to Date, Last 12 Months & Prior 12 Months)

       

      I have also parameterized the periods so that I can select to ‘look back’ for a number of months- which will shift everything back in time as needed.

       

      I have a solution that partly works, but it requires me to create a calculated field for each spend type across each of the 4 periods- resulting in 8 calculated fields.

       

      Where I am seeing issues is when I attempt to calculate the % DirectSpend against TotalSpend. It seems like the simple table calcs will not work because I have multiple calculated fields representing essentially the same thing but have different slices of time. This seems like it will require another set of calculated fields for the %’s- which seems to further complicate matters.

       

      Another issue that is getting in the way is that even though I am attempting to represent 4 different periods of time, some of them overlap. For example, the Current Fiscal Year to Date period will partially overlap with the Last 12 Months period- which means that I cannot simply create a calculated field to determine which time period the amount should appear in- since some values will be summarized in multiple time periods.

       

      The excel file attached shows what the data currently looks like and what I would like it to look like in Tableau. 

      I have read some suggestions that revolve around un-pivoting my data but I’m not sure that will help me.

      I have the ability to write whatever sql is needed to re-format the rows/columns but I’m just not sure what direction to go.

      Thanks for any assistance.

        • 1. Re: Need cube-like behavior with dates and summaries
          Eric Maher

          I had to try- I unpivoted the data so that instead of 2 amount columns (as shown in the previously attached document) I only have one amount column with the 2nd column being the descriptor. This approach got me much closer to how I want to display the data.

          I also had to add back in the missing spend amount. Originally I had Direct Spend and Total Spend (leaving out PO Spend). In our world PO Spend is a good thing and the existing reports focused on Direct Spend as they always want to reduce this amount.

          By not having the other half of Total Spend, table calcs were proving to be very difficult.

          At this point, I have all the data I need, but am stuck on layout of the data.

          For example, to get my Total Spend to display (by adding a table calc), all of the totals for each of the time periods appear grouped together to the right instead of next to the period for which they apply. You can see this in the attached image.

          In addition, I will ultimately want to hide (if I can) the PO Spend portions.