5 Replies Latest reply on Apr 28, 2017 7:27 AM by asad khan

# Out of Stock Analysis Tableau

Hello!

Please help me to make right calculations. I tried but could not it myself.

I have the following data: Shop, Weight1, Hand, Maufecturure,Brand,Flv(Flavor),Size, Purch(Purchase of this Type in Last one week), T_Stk (Total Stock of this Type in Shop).

I need to calculate/ analyse out of stocking at different levels (At Maufecturure OR Brand) Level.

The formula depicts, if T_Stk (Total Stock) is "0" for a specific Maufecturure (A-Man) for each of its product (Brand + Flv + Size) then it will be marked as Out of Stock on that Shop. In this way,

Out of Stock = Sum of distinct (Shops weight (Weight1) that are out of Stock) / Sum of Distinct (Weight (Weight1) of all Shops)

I need help for the calculation of this OOS.

• ###### 1. Re: Out of Stock Analysis Tableau

Not sure but find my approach as reference below and stored in attached workbook version 10.2 located in the original thread.

1. Formula:

if {fixed[Shop],[Maufecturure],[Brand],[Flv]:sum([T Stk])}=0 then {fixed[Shop],[Maufecturure],[Brand],[Flv]:sum([Weight1])}/{fixed:sum( [Weight1])} end

Regards,

Norbert

• ###### 2. Re: Out of Stock Analysis Tableau

Thank you Maijoor for prompt reply to my question.

@Your solution is perfect at SKU level (At lower layer Manf + Brand + Flv). But it fails when I come to higher level (At Manf level). On Shop 19, your formula is being calculated for two SKU's, however this shop should NOT be marked as Out Of Stock at Manufacturer Level.

Waiting for your kind reply on it.

Regards,

• ###### 3. Re: Out of Stock Analysis Tableau

Find my updated approach as reference below and stored in attached workbook version 10.2 located in the original thread.

if {fixed[Shop],[Maufecturure]:sum([T Stk])}=0 then {fixed[Shop],[Maufecturure],[Brand],[Flv]:sum([Weight1])}/{fixed:sum( [Weight1])} end

Regards,

Norbert

1 of 1 people found this helpful
• ###### 4. Re: Out of Stock Analysis Tableau

Hi Norbert,

With few modification in the formula, now its working well for Manufacturer level. Further, I need to have hierarchy for Manf + Brand (which I have added in the attached file). The formula with Manufacturer and Brand Fixed  (Formula Working) works well for Manf + Brand hierarchy expanded. However if I collapse the hierarchy to Manf level it gives wrong results.

Need help to resolve the issue.

Thanks again.

Regards,

• ###### 5. Re: Out of Stock Analysis Tableau

Hi Norbert,

Sorry for bothering you, please update on my below query.

Thanks and Regards,