Using Date Hider for POP Calcs

[georgeAWS4]

Hi Guys,

I’ve created an Arena for the below query if anyone could have a go and try and help me out:

QS Community Weekly Volumes Dummy

I’m trying to show various metrics for the last four weeks in the table as below, but am struggling to make a previous year YTD calc work:

The value showing currently is just the total for the week referenced in that row (same as column 1 ‘Lbs’ in the table - no formula just sum(Total Quantity: AU = Received) for the week). This is the formula I’m using:

And I’m using the Date Hider filter @Jesse suggested here (How do I superimpose a period over period in a visual without having the period that I'm comparing it to in the visual?)

I’ve also tried getting the YTD difference in the table so I could calculate last year’s YTD value by subtracting that from this year’s. This is coming up as blank in the table though…

I’m wondering if I need to use the Date Hider differently, although I’ve seen what I’m trying to achieve work perfectly fine here (How do I superimpose a period over period in a visual without having the period that I'm comparing it to in the visual? - #14 by rusu2k)

Any help would be greatly appreciated!

[Brett]

Hi @georgeAWS4,
This one is a bit tricky since you’re trying to reference date fields that have been filtered out of the visual. Check out the attached Arena view with a work around that could potentially work for your case:
Using Date Hider for POP Calcs

Let us know if you have any additional questions!

[Brett]

Hi @georgeAWS4,
It’s been awhile since we last heard from you on this thread; did the example put together in the Arena view above help point you in the right direction?

If we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you!

[georgeAWS4]

Yep this was the solution, thank you!!!

[snwboarder159]

@Brett - Can you explain the work around, please? I tried accessing the view, but it came up with no data error message. I have the same situation as OP, which is I would like to remove prior periods from Pivot Table view when using period-over-period calculation, but still have the PoP calcs function.

[Brett]

Hi @snwboarder159,
When you click on the link, make sure to hit the ‘copy analysis’ button on the left hand side. That will pop open the analysis and allow you to see the calculated fields and filter that were used to build the visual.

[snwboarder159]

Thank you @Brett! So essentially using periodtodate functions for aggregated period counts/sums (week, month, year), then Lag function for prior periods and simple arithmetic for variances. It certainly works if a bit more involved! Thank you!