periodOverPeriodLastValue offset not static

I am trying to use the periodOverPeriodLastValue function and running into some trouble. I have two parameters, StartDate and EndDate. I want to use a time series bar chart to compare daily sales to the sales from X amount of days prior based on my start and end date parameter selections. So if the StartDate-EndDate = 30 days, it would give me sales from 30 days ago, if StartDate-EndDate = 365 days, it would give me sales from 365 days ago etc…
This is what I’ve started with

periodOverPeriodLastValue(sum({sales}), {date}, DAY, avg(dateDiff(${StartDate}, ${EndDate}, “DD”) + 1))

When I run this I get the error “At least one of the arguments in this function does not have correct type. Correct the expression and choose Create again.” I am curious if the offset has to be a static number? Is there a way around this?

Additionally there is another issue I’ve run into several times. When I use the period over period functions they return blank constantly no matter what. Below is an example. Image 1. is my calculated field. Image 2. shows a previous date of Jan 3, 2019 and previous quantity shipped is blank. But in Image 3. if I go to the row where date is actually Jan 3, 2019 you can see that quantity shipped was 16,026.00, which should’ve been the value shown for previous quantity shipped in image 2.

image
2.
image
3.
image

Hi rbrady,

Period and offset determines the kind of PoP calculations you are creating. For instance, a combination of

  1. <YEAR, 1> entails YoY
  2. <MONTH, 1> entails MoM
  3. <WEEK, 1> entails WoW
    etc.

So they need to be static.

Making Period and/or Offset dynamic through a calculation would change meaning of PoP calculations for every row and can lead to ambiguous definition. I am curious about the use-case you are trying to solve here.

Regarding blank cell values, since you are doing YoY computation, can you please change your calculations to <YEAR, 1> instead. Days and years are not perfectly aligned, as they might include leap years for instance 2020. Using Year granularity automatically accounts for those differences.

-Raj

Hi Rajkumar,
That makes sense about the leap years. I changed it to YEAR and I’m unfortunately still getting the same problem as before.
image
image
image

My use case for the dynamic offset is comparing sales over a specific time period, to the previous period. I have two date parameters, start and end date. My uses can set those dates to whatever they like. For example lets say they pick 3/1/2022 - 3/14/2022 I want my bar chart to show them sales on each day of that period, compared to sales on each day of the previous period. So the number of days I want to look back would change based on the distance between start and end date parameters. Ideally it would be something like this.