Bar Chart -> Sum by Day/Week/Month/Quarter

I have my data in a daily form for a predefined period of dates (2021-06-01 until 2021-10-31).

@date returns values as 2021-10-25

@othervalue returns values as a simple number, for that day.


1. The simple version of what I'm trying to do is show a bar chart for the @othervalue grouped by month. Basically, I want to see 5 bars with sums for June, July, August, September, and October.

2. A more complicated version of what I'm trying to do is, I have a user input that allows me to pick whether I want to group my values by day, week, month, or quarter. How do I change the bar chart in #1 above to reflect this?

I've looked at a bunch of posts here, and nothing I did (tried using GROUP and GROUPBY) did not accomplish what I was trying to do.

Thank you!

1 comment

  • Avatar
    Shima Beigzadeh Official comment

    Hi Andrei, 

    Thanks for posting in our community! 

    For Q1: In your Bar/Line Chart you would select your date and values for series and x axis as below: 

    • Series sub-component: In Data tab, select the column of data that contains the values (i.e. @othervalue)
    • X Axis sub-component: select the column of date that contain the dates. (i.e.@date )

    Then in properties tab, make sure

    • Format as: Date/Time
    • Input Format: yyyy-MM-dd
    • Display Format: yyyy-MM

    Click on Group button. This will group your data by month. 

    For Q2: you need to make a dynamic X Axis which would work with a Date User Input Control.
    The steps would be: 
    - Create your drop down of dates. 
    - Then in your X Axis sub-component choose you dates from date column, and in Series sub-component choose your values.
     - With Bar/Line Chart selected in your component tree, click Add Hidden Data.
    - In the data tab, you would use a formula using SWITCH () function which switches the variable value already set in your Date User Input Control
    This Community Post helps you to build a user input control to filter and count by a date range (day, week, month, or quarter).

    Please have a look at the above link and if you still have questions, you can always reach to us at support@klipfolio.com

    Hope that helps!

Please sign in to leave a comment.