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

swagner
swagner Contributor

 

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

Slide1.JPG

 

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

Slide2.JPG

Best Answer

  • AS
    AS Coach
    Answer ✓

    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.

    Aaron
    MajorDomo @ Merit Medical

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Answers

  • AS
    AS Coach
    Answer ✓

    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.

    Aaron
    MajorDomo @ Merit Medical

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • swagner
    swagner Contributor

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

  • swagner
    swagner Contributor

    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.

     

    you rock.jpg

This discussion has been closed.