0

Displaying Date Sort functions by Year

I have an excel spreadsheet that breaks up our data as follows:

Year | Month | Department (Sales)

I am trying to figure out a way to pull the data into Klipfolio to allow the user to Select a year and display Jan - Dec on a bar graph.

Showing Year over Year on the bar graph would be super. 

Any help is appreciated.

David

 

1 comment

  • Avatar
    Parker Selman Official comment

    Hi David! 

    Thanks for reaching out - I've put together an example to help show how to build this type of Klip. This Klip was built with our sample "Live Sales Data":


    Let's start with the user input control. In the values of the user input control, I am using the DATE_STARTOF formula to convert all of my dates to the start of their respective year (note: this formula requires dates in Unix format - if your dates aren't in Unix format, you can convert them with the DATE function). 

    In the labels of the user input control, I'm using a formula to convert my dates to "yyyy" format (you can use DATE_CONVERT or DATEVALUE, depending on whether or not your dates are in Unix format) 

    In the properties of the user input control, I've set it to use a custom variable $year

    In the bar chart, I've set the X axis to my dates, formatted them in "MMMM" format, and grouped them. 

    You'll see that I've set up two series, one to show the revenue for the selected year, and another to show the year-over-year change from the year prior. For the "Selected Year" series, I've used the following format (where column A are my dates in Unix format, and column C is my revenue):


    This formula returns only the revenue values that fall within the year specified. 

    To get the year-over-year line, this requires a hidden data element to calculate the prior year's revenue: 


    Then, in my series "YoY" change, I use results references to do the division and formatted it as a percentage: 



    Because each series uses a difference unit value, I added another Y axis for the % change, and set this series to use that % axis. 

    The end result is the Klip you see above! I should note that if you're just looking to display one year in a series without the YoY change, you should follow the steps in the video below:

    https://www.youtube.com/watch?v=2S8FH2Fk0KA&ab_channel=Klipfolio 

    I hope this helps - if you have any more questions or are having difficulties setting something like this up, please contact support@klipfolio.com and we can have a look at your Klip!

    Parker

Please sign in to leave a comment.