How can we help?

New post
0

Create Bar/Line Chart Klip with sales (grouped per month)

Avatar
Alexander D.
None
Follow

I am super stuck, trying to create a bar/line chart that shows me the number of sales per month.
My modelled data source looks like this:

 


Now for the "Series" setting of Bar/Line Chart I could do "COUNT(@Order ID)" and it will show me the (total) number of lines = total number of orders (731) in my data source, but only as one datapoint (1) on the X Axis.

I cannot come up with the (probably GROUPBY or something) formula, to actually split the total over the months. I want to aggregate the "@Order Created At" column to months so that it plots the COUNT for each of the months present in column "@Order Created At" as the line chart.

Any help is greatly appreciated 🙏🏻

Date Votes

5 comments

  • Avatar
    Parker Selman Official comment

    Hi Alexander, 


    Thanks for the post! 

    I would set this up as follows:

    For your chart X axis, reference the "Order Created At" column - in the properties of that axis, ensure the format is Date/Time (the Klip editor should map to this automatically) and set the output format to something that only includes months and years, like 'MM-yyyy'. 

    For your series, reference the Order ID column, and make sure the aggregation property is set to 'Count'

    Then, group the X axis by selecting the 'Group Repeating Labels' property. You may also need to sort your axis Oldest to Newest. 

    Once that's done, you should be left with a bar chart that shows a monthly count of orders. 

    I hope this helps! 

    Parker

    Technical Support Team Lead - Klipfolio 



  • 0
    Avatar
    Alexander D.

    This did the trick and was surprisingly easy. I was wondering why that didn't work for me and think I might have not used your trick with properly setting/configuring the X-Axis. Thanks so much.

  • 0
    Avatar
    Alexander D.

    I'm sorry to cycle back to this and would appreciate a hint: After your (very good) instructions I've tried to include the data also from the previous year to plot a comparison graph in the same chart.

    The data (source) is formatted in the same way like in my screenshot, just from 2023 and contains ALL transactions of the 12 months of 2023 already. I've added another "Series", pulled up the 2023 data source and configured it in the exact same way. However, the data points of 2023 don't show correctly. The graph stops at July 2023 and shows the same number of orders (which is not correct).


    So I went to the "Axis" and tried to CONCAT the "Order Created At" (@data2024,@data2023) and grouping it as per your instructions, hoping that would show the full year then and correct the data but was out of luck.

    What am I missing?

    Alex

  • 0
    Avatar
    Parker Selman

    Hi Alex,

    Thanks for following up on this. It sounds like the issue here is that your 2023 data is not in exact alignment with your 2024 data, which is what you're using to configure your X axis. Because of this misalignment (ie the two sources having different numbers of records and differences in their date columns), you'll need to create a new series on your chart for 2023 and utilize the LOOKUP, GROUP, and GROUPBY formulas to display your values. 

    In your case, your series formula for 2023 would be written something like: 

    LOOKUP (&X axis,
    GROUP(DATE_CONVERT(@data2023:Order Created At, "yyyy-M-d", "MMM") ,
    GROUPBY(DATE_CONVERT(@data2023:Order Created At, "yyyy-M-d", "MMM") , @data2023:OrderID , 'count' ))

    I hope this helps! 

  • 0
    Avatar
    Alexander D.

    Thank you Parker. Correct, it's because the data points in 2023 data are not related to data points in 2024. Could be completely different days, times, etc. that are not present in both data-sets. To make things less complex, I understand it would make sense to try to aggregate/align the data on the input side already, before using it in the data source. Thanks anyways for the great insights!

Please sign in to leave a comment.