How can we help?

New post
0

Building klip with sales grouped by month and compared to last year

Avatar
Andrea Pfundmeier
None
Follow

Hi, 

I have tried to solve this by myself for some time now, but I think there are smarter people out there)

I want to create a bar chart with 2 series: one with the monthly sales from 2017 and one with the monthly sales from 2018.

The x-Axis should be the months (January - December).

I have 2 Data Sources (both from Salesforce. One is called "All Sales This Year" the other "All Sales Last Year"): one with all closed deals from 2017, one with all closed deals from 2018.

The date format in the data sources is yyyy-mm-dd and the list also included the amount (in EURO) of the closed deal.

I cannot figure out how to set it up. I am totally confused where to start: Lookup? or Date_Convert? or Group_by? I understand how the single formulars work, but I cannot figure out how to bring it all together that it shows the correct data.

Can someone help me? Thanks!

 

Date Votes

6 comments

  • 0
    Avatar
    Josh Cohen-Collier

    Hi Andrea,

    Thanks for posting in our Community!

    You should be able to set up your Klip in the following manner, however if you are struggling with this, or getting incorrect results, I would be more than happy to create a support ticket so we can dig in a little further manually.

    Your X-Axis dates should be the months of the year, January to December, which are months 1-12.
    CUMULATIVE(REPEAT(1,12))
    Then, in the Properties panel of the X-Axis, you can set the format to be Date/Time, and the output format to be whatever you like(eg MMMM for December).

    Series 1 would be as follows;
    LOOKUP(
            &X-Axis,
            GROUP(Months from this years sales),
            GROUPBY(Months from this years sales, Values from this years sales)
    )

    Series 2 would be this;
    LOOKUP(
            &X-Axis,
            GROUP(Months from last years sales),
            GROUPBY(Months from last years sales, Values from last years sales)
    )

    Hope this helps!

    Best regards,
    Joshua
    _________________
    Joshua Cohen-Collier,
    Technical Support Specialist, Klipfolio

  • 0
    Avatar
    Marek Nguyen

     Hi Joshua,

    I'm trying to follow your instructions/suggestions on how to add last years data to compare it with actual values but it seems like I don't really get it.

    Also I'm fairly new to Klipfolio. Could you explain it in a little more detail or is there something in the documentation I could read through?

     

    Best Marek

  • 1
    Avatar
    Zach Kathnelson

    Hey Marek, 

    Since Josh posted his original response, we've released some great new functionality that could make this much easier!  Have you tried our Applied Actions?  https://support.klipfolio.com/hc/en-us/articles/222017088-Filter-Group-Aggregate-and-Sort-actions-videos-.  

    Re-reading Josh's post, the instructions require this: 

    1) the data you show on the x axis of your chart needs to be simply the Months.  Whether you choose to show Jan, Feb, March, or something like 01, 02, 03, the only data should be the months.  

    2) The two series then both use the LOOKUP function to show the corresponding data from this year and last year.  LOOKUP in Klipfolio is similar but not identical to vLOOKUP in excel.  Klipfolio's LOOKUP has 3 parameters - I wrote this blog post a little while ago that tries to clarify: https://www.klipfolio.com/blog/most-important-formula-in-klipfolio.  

    I find it a bit tough sometimes to answer formula questions without looking at the data, so if you are open to enabling Support Access in your account, and sharing the name of the Klip, I'll take a look and then can post more meaningful feedback here on what might be going wrong.  (last link I'll share...: https://support.klipfolio.com/hc/en-us/articles/215634348-Granting-Klipfolio-Support-access-to-a-user-account)

    Zach

  • 0
    Avatar
    Marek Nguyen

    Hi Zach,

     

    thank you for your response. With your explanation and the blogpost link you shared with me I was able to create my Klip very easy. It's easier if you understand how it works.

    Thank you for your help and have a great week.

     

    Best Marek

  • 0
    Avatar
    Rien Nijman

    Hi,

    The blogpost dissapeared.

    I cannot find information simply about showing data from multiple years ?

     

  • 0
    Avatar
    Meggan King

    Hi Rien,

    It looks like the link needed a quick update. It should work now, and you can see it here as well:

    https://www.klipfolio.com/blog/most-important-formula-in-klipfolio

     

    Thanks

    Meggan

Please sign in to leave a comment.