Hello Jackie,
PARAPHRASE (Metric: Seasonal % change in Inventory):
= IF (
$(vLastSeasonsInventory)=0 AND $(vThisSeasonsInventory)>0,
1
, //else
$(vThisSeasonsInventory)/$(vLastSeasonsInventory) - 1
)
CALCULATION METHODOLOGY for tabulating seasonal change in item inventory volume:
WHEN either not using pivot table or there are no horizontal dimensions in the pivot table:
if(SecondaryDimensionality()=0
Use inventory calculation method A:
Calculate a season’s change in inventory by summing up all the activity and subtracting the starting point from the ending point
rangesum(-first(sum(skuCount)),last(sum(skuCount)))
ELSE
Use inventory calculation method B:
Calculate a season’s change in inventory by summing up the activity at the row level
sum(skuCount))
CALCULATION (% Ratio for seasonal change in Inventory):
IF last season's inventory was nothing
(
if(SecondaryDimensionality()=0,
rangesum(-first(sum({$<season={$(=[Previous])}>} skuCount)),last(sum({$<season={$(=[Previous])}>}skuCount))),
sum({$<season={$(=[Previous])}>}skuCount))
)=0
AND this season's inventory was something,
(if(SecondaryDimensionality()=0, rangesum(-first(sum(skuCount)),last(sum(skuCount))), sum(skuCount)))>0
THEN //all inventory tabulated this season is completely new inventory
The ratio of inventory increase is 1 (100%)
ELSE //there was inventory on-hand at the start of this season
Get the final tally of this season's inventory
(if(SecondaryDimensionality()=0, rangesum(-first(sum(skuCount)),last(sum(skuCount))), sum(skuCount))
Subtract the final tally of last season's inventory
if(SecondaryDimensionality()=0,
rangesum(-first(sum({$<season={$(=[Previous])}>}skuCount)),last(sum({$<season={$(=[Previous])}>}skuCount)),
sum({$<season={$(=[Previous])}>}skuCount))))
take the calculated volume change in inventory and divide it by the inventory on hand at the end of last season, to calculate the % change in inventory over the last season
if(SecondaryDimensionality()=0, rangesum(-first(sum({$<season={$(=[Previous])}>}skuCount)),last(sum({$<season={$(=[Previous])}>}skuCount))), sum({$<season={$(=[Previous])}>}skuCount)))
ENCAPSULATED INTO VARIABLES:
vThisSeasonsInventory = if(SecondaryDimensionality()=0, rangesum(-first(sum(skuCount)),last(sum(skuCount))), sum(skuCount))
vLastSeasonsInventory = if(SecondaryDimensionality()=0, rangesum(-first(sum({$<season={$(=[Previous])}>}skuCount)),last(sum({$<season={$(=[Previous])}>}skuCount))), sum({$<season={$(=[Previous])}>}skuCount))
FINAL EXPRESSION:
= IF (
$(vLastSeasonsInventory)=0 AND $(vThisSeasonsInventory)>0,
1
, //else
$(vThisSeasonsInventory)/$(vLastSeasonsInventory) - 1
)
