Row context to calculate WoW, YoY, PoP

Question & Answer
6

Hi @Andrea,

Thanks for the further description.
If that’s the case, I think you would need Level Aware Aggregations (LAA) here.

The easiest way that I can think of right now:

  1. Extract just the week out from your “Week” column using split or right function - namely “Week Number”
  2. Get the latest year in your dataset, namely “Get the latest year in the dataset”:
extract("YYYY",maxOver({Order Date - New},[],PRE_AGG))
  1. Compute sales for current year, namely “Weekly Sales CY”:
ifelse(extract("YYYY",{Order Date - New})={10.0 Get the latest year in the dataset},Sales,0)
  1. Compute sales for previous year, namely “Weekly Sales PY”:
ifelse(extract("YYYY",{Order Date - New})={10.0 Get the latest year in the dataset}-1,Sales,0)
  1. Compute YoY Difference:
(sum({10.2 Weekly Sales CY})-sum({10.3 Weekly Sales PY}))/sum({10.3 Weekly Sales PY})

Outcome:

image
image790×518 42.8 KB

To understand more about LAA:

1 Like