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
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.
Period and offset determines the kind of PoP calculations you are creating. For instance, a combination of
<YEAR, 1> entails YoY
<MONTH, 1> entails MoM
<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.
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.
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.
Hi rbrady, Are you filtering your data based on the StartDate and EndDate parameters? If so, be aware that these filters might also filter out the data that your periodOverPeriod function requires for the comparison and, hence, shows a blank cell. This article explains a way how you can prevent this from happening, while still filtering each column as desired, using ifelse logic within calculated fields.
Did this answer your question? If so, please help the community out by marking this answer as "Solution! If you found a different root cause or solution in the meanwhile, we would be happy if you could share it with the community as well.
