2 Replies Latest reply on May 23, 2016 3:59 AM by . Balfour

    How to Find inventory figure at the Exit of a Quarter ?

    . Balfour

      Hi all,

       

       

      I have been experimenting a lot the last week trying to figure out if I can find the "Quarterly Exit" inventory levels of the data I have within Tableau. This would require me to be able to state the Exit inventory levels just as the quarter ends, as I require that in order for some pretty key analysis within my team.

       

      I'll give an example of what I have attempted so far, and the shortcomings of each of the methods I have tried.

       

       

      1) One static "Exit Inventory" field within Excel for each record. By creating  pivot table of the data, and highlighting the last month of the Quarter, and VLOOKUP-ing it against each record - so for example,  each individual record in Q414 within excel would be allocated a static Exit CI of "17,426,060" in a field within the raw data.
      This worked fine when I required the Exit Inventory to be divided by the units at a top level, but filtered down to individual product levels, it would produce extremely incorrect results, as the Average of the Exit Inventory when divided by the units of an individual product line would obviously display extremely erratic results.

       

      03-13b.png

       

      2) Producing the same pivot, except including each product category within the pivot too, meaning each product category had its own Exit Inventory figure for each Quarter. This had the opposite result, meaning my figures at a product level made sense, yet at the top level - the figures were completely incorrect.

       

       

      I have therefore come to the realization, that having a static Exit Inventory figure within Excel is not the answer - rather I need a dynamic calculation within Tableau that will produce one Exit Inventory figure for each Quarter, and will adapt depending on what filters I have set on the view.

       

      Would anyone know if there is a calculation somewhere within Tableau that would allow me to undertake this? It would be extremely helpful.

       

       

      Thanks in advance,

       

       

      Ross.