9 Replies Latest reply on Jul 12, 2017 12:48 AM by Zhouyi Zhang

How to use fixed function for a field which is calculated by table calculation

Hi Guys,

I have opening stock as on first day of month, Receipt, Consumption and Min Stock required.

I've calculated closing stock as below and need to fixed closing stock or closing status. when i am trying to fix closing status it is showing error " Level of detail expressions cannot contain table calculations".

Cal.1 Change in Stock = [Receipt]-[Consumption]

Cal.2 Closing stock= RUNNING_SUM(SUM([Opening Stock as on 1st]))+RUNNING_SUM(SUM([Change in Stock]))                 (table Across)

Cal.3  IF [Closing Stock]< ATTR([Min Inventory level]) THEN 1 else 0 END

Cal.4  Closing Status_Fixed ={ Fixed Plant,Material Code, Date: sum(  If Closing Stock<Min Inv then 1 else 0 end)} but it is showing error " Level of detail expressions cannot contain table calculations"

Requirement : Need only those products for which at least once closing stock<Minimum Inventory level, numbers in pane will reflect no of such days

Please suggest how can we fix closing stock or closing status.

Thanks for the time and consideration

• 1. Re: How to use fixed function for a field which is calculated by table calculation

Hi, Himanshu

Not sure I understand your requirement correctly or not.

Workbook attached with 10.2.

Hope this could help

ZZ

• 2. Re: How to use fixed function for a field which is calculated by table calculation

Hi Zhouyi,

I would like to thank you for your time and consideration.

solution is not seems right. it should be 11 for Plant B001, Prod-AB and month Jun'17.

I've make small changes in formula of closing stock and closing status. TWBX is attached with version 10.3.

My requirement: Need to calculate number of days for which closing stock <Minimum Inventory

Solution looks like this. calculated in excel

 Plant Prod Jun-17 Jul-17 Sep-17 Oct-17 Grand Total B001 AB 11 31 30 31 103 XYZ 0 17 25 31 73 B002 AB 10 29 30 31 100 XYZ 21 0 25 31 77 Grand Total 42 77 110 124 353

Thanks and Regards,

Himanshu

 Sum of Closing Status Flag Month Plant Prod Jun-17 Jul-17 Sep-17 Oct-17 Grand Total B001 AB 11 31 30 31 103 XYZ 0 17 25 31 73 B002 AB 10 29 30 31 100 XYZ 21 0 25 31 77 Grand   Total 42 77 110 124 353
1 of 1 people found this helpful
• 3. Re: How to use fixed function for a field which is calculated by table calculation

Hi Zhouyi,

I am still waiting for a solution. Please suggest to resolve this...

Regards,

Himanshu

• 4. Re: How to use fixed function for a field which is calculated by table calculation

Unfortunately, its not possible yet.

LOD cannot be calculated on top of a Table Calc.

Vote here:

LOD with Table Calc https://community.tableau.com/ideas/6968

LOD with Sets https://community.tableau.com/ideas/7239

1 of 1 people found this helpful
• 5. Re: How to use fixed function for a field which is calculated by table calculation

However your problem can be solved if you move your table calc to be a regular expression.

Then you can create LOD to compute number of days for which closing stock <Minimum Inventory

1 of 1 people found this helpful
• 6. Re: How to use fixed function for a field which is calculated by table calculation

Hi Rahul,

Calculation of closing stock can not be a regular expression. If you can suggest something than it will be appreciable.

KPIs are given below..

Closing Stock= Opening Stock+ Receipt -Consumption

Opening Stock= Prev. Day Closing

Regards,

Himanshu Sharma

• 7. Re: How to use fixed function for a field which is calculated by table calculation

Hi, Himanshu

I tried with the sample workbook, but I can't make every number match to your sample result. please see highlighted below.

Not quite sure is it because the data difference or I am still not git it right. But from the Position Table sheet, it look right.

Workbook attached for your reference. Please let me know if you have question

ZZ

• 8. Re: How to use fixed function for a field which is calculated by table calculation

Hi Zhouyi,

Thanks for this..

Please tell me how it is calculating and share steps for this..

Regards,

Himanshu Sharma

• 9. Re: How to use fixed function for a field which is calculated by table calculation

Hi, Himanshu

Step 1

Step 2

Create window sum for closing status

Step 3: set up table calculation

Step 4: add first() = 0 to detail and set up table calculation

step 5: right click the first()=0 and filter as True.

Hope this could help.

ZZ