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.

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

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.

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

You can fix to December using a formula such as:

IF MONTH([Period]) = 12 AND [Account] = 'Inventory' THEN [Value] END

You may need to tweak that to better match your field names.

For summing COGS check out the WINDOW_SUM calculation - that enables you to sum values that are already summed.

1 of 1 people found this helpful
• ###### 2. Re: I need to pick exact values out in a somewhat dynamic way?  Almost like data from an array.  Maybe.

Exactly what I needed 100%.  Thanks so much, Andrew!  Worked like a charm, though I had to SUM the IF statement because it got mad about mixing aggregate and non-aggregate data in a calculation.  Other than that, it's working perfectly.

Thanks again!

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

Great, glad you got it sorted