3 Replies Latest reply on Nov 16, 2016 7:59 AM by Andrew Watson

    I need to pick exact values out in a somewhat dynamic way?  Almost like data from an array.  Maybe.

    Zachary Robinson

      Hello Community,

       

      Tableau ultra noob here.  We just purchased the software 1.5 months ago and I just got back from Tableau conference.  Great time and I learned a bunch, but I'm still having trouble with some calculations for Days Sales Outstanding, Days Inventory outstanding, etc.  I'll get to my problems in a moment, but here is an idea of how the data is laid out:

       

                                                   Jan-12     Feb-12     Mar-12     Apr-12     May-12     Jun-12     Jul-12     Aug-12     Sep-12     Oct-12     Nov-12     Dec-12    

      Company 1     Inventory          1               2               3               9               8               5               8          6               8               5               8               3

      Company 2     Inventory          1               2               3               9               8               5               8          6               8               5               8               3

      Company 3     Inventory          1               2               3               9               8               5               8          6               8               5               8               3

      Company 1     COGS               7               6               5               6               9               3               6          8               6               6               8               4

      Company 2     COGS               7               6               5               6               9               3               6          8               6               6               8               4

      Company 3     COGS               7               6               5               6               9               3               6          8               6               6               8               4

       

      Ignore the actual values, please. 

       

      I have then pivoted this data in Tableau so it looks like:

       

      Period     Values     Company Name     Account

      Jan-12          1          Company 1               Inventory

      Jan-12          1          Company 2               Inventory

      Jan-12          1          Company 3               Inventory

      Jan-12          7          Company 1               COGS

       

      And so on, you get the idea.

       

      So here is where my problem lies.  For Days Inventory outstanding, my calculation needs to be:

       

      Inventory AS OF DEC-12

       

      /

       

      SUM(COGS) (by year)

       

      I have a bunch of calculated fields parsing the "Account" Dimension to return values for various accounts, i.e.:

           SUM(IF [Accounts]="Accounts Payable" THEN [Values] END)

       

      I'd love if I could have a calculation that picks out inventory in Dec based on the YEAR in the field "period", something like

       

      Inventory as of Dec [YEAR of Period]

       

      /

       

      SUM([COGS] in that year)

       

      Another problem I have with this is I can't SUM COGS, because it is a calculated field that is already aggregated, but I feel like this is a simple problem that I'm missing something silly on.  It's possible that by doing the sum in my calculated field all I need as the second part of my equation is [COGS] since it's already the sum of those values and hopefully ordered by year based on the YEAR(Period) pill in my columns shelf.

       

      Thanks in advance, I'll be monitoring the thread all day so feel free to ask for clarification.