I have been exploring the new period over period functions and I’m having some trouble. Whenever I use the PeriodOverPeriodLastValue function, I don’t get an error but it always comes back empty, even when there should definitely be values there. Has anybody successfully been able to use this function or have an idea why this might be happening?
Hi rbrady, What is the chart type and calculation you are using? Depending on your visual aggregation granularity and the granularity used for look back period in PeriodOverPeriod calculations, it’s expected that you would see first rows of blank values. You can hide those blank rows by applying filter on denseRank of Date column to view just the latest non-blank rows.
Hi RajkumarH,
here is a screenshot to better show the issue I’m facing. I am using a table visual with date aggregated by month, and my PeriodOverPeriod looks at the previous year. I created a sample table (left), and oddly enough was able to get column to work as expected. but it still does not seem to be working in my actual table (right). I do have some columns hidden here for security, but as you can see the values for {running sum of number} are the same in both examples so I’m not sure why the PeriodOverPeriodLastValue numbers would not be the same as well. I must be doing something different but I can’t figure out what it would be. I’ve disabled all my filters so I know that isn’t the issue.
Hi Brady,
What is the difference between your left and right screen shots? Are formulas same, do you have by any chance additional filters on your right visual?
Below is the formulas and screen shot which demonstrates how PeriodOverPeriodLastValue work.
The formulas I used are
runningSum = runningSum(sum(Profit), [{Order Date} ASC], [])PoPProfit = PeriodOverPeriodLastValue(sum(Profile), {Order Date}, QUARTER, 1)runningSumPoP = PeriodOverPeriodLastValue(runningSum, {Order Date}, QUARTER, 1)
Hi RajkumarH,
There were additional filters, but I disabled all of the filters so I don’t think that was the issue. The formulas were exactly the same so I don’t think it’s that either. The visual on the right contains columns that change based on parameter settings, so I don’t know if that would disable the ability to use the PeriodOverPeriod functions. I’m not sure how to figure out what would be causing the discrepancy.
Hi @rbrady , I would like to follow up with this post. Do you have any updates? Have you figured out what is causing the discrypency?
Hi,
I do not unfortunately, I gave up eventually.
Have you opened a QS support ticket on this issue? Everything there is protected by an NDA, so I could never your data and you could never see my data?
yes, unfortunately we never came to a conclusion
Update: We were able to identify the issue. It was due to having a hidden column “year” which is the year number extracted from the date. The PeriodOverPeriod function groups by every field in the table. For example if I have a period over period function that’s looking back a year. Where my date is Jan 2022, and my year is 2022, then my period over period function will look for a value where date is Jan 2021 and year is 2022 which it will obviously never find resulting in the column always being blank. For the time being the only way to solve this would be to exclude the year column from the table. Since I do not wish to do that I am using the lag function as a workaround which also does the job.
1 Like