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).

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.

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.

swagner

Thanks for the information Aaron!  I am going to start working on this now.

swagner

Aaron,

 

I wanted to say thanks again!  I got this working, and it is doing exactally what I had hoped.

 

Thanks so much for taking the time to provide a detailed answer to my post.