
1. Re: Forecasting Inventory Balances
Shinichiro Murakami Apr 13, 2017 12:57 PM (in response to Brian Biggs)Hi Brian,
Thank you for finding old post.
I hope I understand you issue correctly.
You want to show the last week of Quarter when you pick "Quarter" on parameter.
To achieve this, you can filter with customized index.
[index Filter]
if [Quarters or Weeks]="Weeks" then 0 else last() end
Thanks,
Shin

Sales Inventory_SM_10.0.twbx 42.6 KB


2. Re: Forecasting Inventory Balances
Brian Biggs Apr 14, 2017 12:10 PM (in response to Shinichiro Murakami)Thanks Shinichiro . That's a very clever approach. You're using a customized index to drive a Table Calc filter which removes all but the last week of the quarter from the view but continues to make them available to the calcs so they work correctly. Great option.
What I didn't share is that I'm using a parameter in my actual worksheet to allow users to switch between measures that need to be summed (like Sales) and this inventory value. If I go with your approach, I guess I could just set up Sales as a Running_Sum table calc. That way, the ending week of the each quarter would show the entire quarter's sales. I think that works.
I wonder if there Is there a way to accommodate this using LOD expressions? Using INCLUDE to bring Week into the view so that the calc performs correctly?

3. Re: Forecasting Inventory Balances
Shinichiro Murakami Apr 13, 2017 3:22 PM (in response to Brian Biggs)Brian,
Nop.
Normal filter filters data itself which easily mess up table calculations like this case.
Using LOD and table calculation at the same time is quite difficult to handle.
Thanks,
Shin

4. Re: Forecasting Inventory Balances
Shinichiro Murakami Apr 13, 2017 7:59 PM (in response to Shinichiro Murakami)After further consideration and investigation, I found the way with LOD.
The problem was field of "Fiscal Year/Quarter/Week"
Was
[Fiscal Year/Quarter] + "W" + STR([Week in Fiscal Quarter])
should be
[Fiscal Year/Quarter] + "W" + right(STR(100+[Week in Fiscal Quarter]),2).
problem of "was" is "max" always brings "9".
Because the the sort result is 1,10,11,2,3,4,5,6,7,8,9,
Then should be remove that problem.
Then
[Date Selector (copy)]
CASE [Quarters or Weeks]
WHEN 'Quarters' THEN [Fiscal Year/Quarter]
WHEN 'Weeks' THEN [Fiscal Year/Quarter/Week (copy)]
END
[The Last week]
{fixed [Fiscal Year/Quarter]:max([Fiscal Year/Quarter/Week (copy)])}
[LOD Filter]
if [Quarters or Weeks]="Weeks"
then "Show"
elseif [Fiscal Year/Quarter/Week]=[The Last week] then "Show"
else "Hide" end
Hide the "Hide" in LOD filter.
Thanks,
Shin

Sales Inventory_SM_10.0_V2.twbx 43.8 KB


5. Re: Forecasting Inventory Balances
Bora Beran Apr 14, 2017 1:22 AM (in response to Brian Biggs)2 of 2 people found this helpfulThis is doable with LOD calcs without bringing week into the viz.
The reason your calculations return different results when you remove week is that the way the calculation is written if it is a full quarter it gets the latest week's value but if it is not it sums up does a running sum over the weeks in that quarter starting from first week.
In a partial quarter you need to take the latest week's known inventory then add expected additions to inventory and subtract expected sales. The rolling week calculations in the workbook always look for the last week in the quarter, when you have a partial quarter, this breaks.
So as first step your rolling week comparison calculation should be made aware of partial quarters. You can do this by checking whether inventory value is null
IF NOT ISNULL([Inventory  Actual])
THEN [Rolling Week]
ELSE 5000 END
or in this workbook whether rolling week is greater than 1
IF NOT [Rolling Week] > 1
THEN [Rolling Week]
ELSE 5000 END
Here 5000 is a random number that will surely fail your comparison of rolling week values with max rolling week since it will always be smaller than max rolling week.
Now if you use this calculated field in your ending inventory calculation
IF [Modified Rolling Week] = { INCLUDE : MAX([Modified Rolling Week]) } THEN [Inventory  Actual] END
This will give you the last known inventory in a given quarter regardless of partial or full quarter.
And a minimally invasive modification to inventory Act/Forecast calculation by just adding a separate section to handle Quarters without Weeks in the view
IF MAX([Rolling Week]) <= 1
THEN SUM([Inventory  Ending])
ELSE IF [Quarters or Weeks] = 'Quarters' then
SUM([Inventory  Ending]) + SUM(IF [Rolling Week] > 1 THEN [Inventory  In]  [Sales Act/Forecast] ELSE 0 END)
ELSE
PREVIOUS_VALUE(0) + SUM([Inventory  In])  SUM([Sales Act/Forecast])
END
END
This makes sure you don't double count incoming items and sales twice by counting them as zero and relying solely on known [inventoryending] until the point [inventoryending] is unknown and has to be estimated by adding [inventoryin]  [sales forecast] to the last known inventory value.
This way you will get the same results with or without weeks being in the view. Below is the result for quarters without weeks in the view. Not to mention getting the correct values for quarterly sales and inventory replenishments.

6. Re: Forecasting Inventory Balances
Brian Biggs Apr 14, 2017 1:15 PM (in response to Brian Biggs)Thank you for these great replies. Shinichiro Murakami  I like your new LOD approach but since it still requires Week to be in the view, I think Bora Beran's wins out.
Bora  I had something like this in my head but I couldn't grok how to get the balances for Sales and Inventory  In only for future weeks. Thank you!
For calculating the last known actual inventory value, I know I can always find it at Rolling Week = 1 so my streamlined Ending Inventory calc looks like this:
{FIXED : MIN(IF [Rolling Week]=1 THEN [Inventory  Actual] ELSE NULL END)}
I like your generalized solution and I will surely use that in the future.