Choose "Year" in Page Level Filter - display Year and Prior Year

swagner

 

I have a brain bender (at least it has been for me).  I have a card I want to display "Sales vs Prior" - if the user chooses a "Year" (2015, 2016 or 2017) in the page analyzer I want this card to display the year they picked, and the year prior to that choice as well.

 

Any ideas on how to approach?  I have attached sample data as well.

 

This is what I would want the user to see if they picked "2017" as the year (2016 and 2017).

 

This is what I would want the user to see if they picked "2016" as the year (2015 and 2016).

Book1.xlsx

AS

The series values interact with the raw data but not the time picker, meaning the series years don't really care what you choose for this year.  They're just going to be absolute values, not relative.

One way I've gotten around this is to bring the data into a dataflow and join it back on itself, adjusted for one year.  The new column(s) would be, then, a "prior year amount" in addition to "amount".

That way, whatever time you choose, there will always be another "prior year amount" next to your "amount", and the prior year calculation will already have been done on the dataside instead of in the card.

 

Something like this

SELECT

a.date

,a.amount

,b.date

,b.amount as prior_year_amount

FROM

sales_table a

JOIN sales_table b on a.date = dateadd(b.date, interval 1 year)

 

There will be more details to iron out than that, but hopefully that gets the ball rolling for you.

Also be on the lookout for period over period cards coming soon.  They just might clear things up.