How can we help?

New post
0

Counting Days when Aggregating into Months

Avatar
James Locklear
None
Follow

Hello,

I am trying to create a Bar Chart that shows the number of videos created by users within a given month.

Here is my data set:

Here is my x-Axis formula because I need a dynamic monthly range for the chart:

And finally, my series formula to match a user:

The problem is that from my data set you can see that I have 2 identical dates so the Lookup function is only returning the first match and I am getting less results than I should for July in this case.

I know that the group and groupby functions are also doing this and I changed the lookup function to only use selects with the same results.

I've been at this for 2 days and I feel like I'm so close yet so far away.

 

Date Votes

3 comments

  • Avatar
    Parker Selman Official comment

    Hi James, 

    Thanks for the post! 

    There's a couple of adjustments I'd recommend making to these formulas to get this to work. See those suggestions below:

    1) In the output format of your DATEVALUE formula, change this to "M/yyyy" to remove the day value from the response. Since you're trying to group your data by month, the exact day of the month is not required for this calculation. We'll account for this in your LOOKUP formula later. 

    2) Either through an applied action or with the GROUP formula, group the X axis so only one value for the month appears on your X axis

    3) In your LOOKUP formula, change the reference to the X axis from a formula reference (labelled with !) to a results reference (labelled with &). This will pass the values in this reference after any applied formatting, meaning your grouping will carry over to your LOOKUP so only one value per month gets passed through the formula

    4) in your SELECT statements, format column C to match your incoming format from the LOOKUP. You can use the DATE_CONVERT formula as such - DATE_CONVERT(@C:C,"MM/dd/yyyy","MM/yyyy"). You'll also want to do this in your GROUPBY formula. 

    This should result in a series formula that returns the complete values for the month you're looking up on. 


    I hope this helps! Please let me know if you have any questions, and if you require more direct support on this, feel free to submit a ticket with our team at support@klipfolio.com. 

    Parker

  • 0
    Avatar
    James Locklear

    Parker,

    Thanks for the reply!

    To your first 2 points I should have included a picture of my actual chart but that is functioning correctly. I believe the applied action I was using to sort them was overwriting the DATEVALUE format so it was showing me all 13 months that I needed. I have since removed that formatting from the formula for clarity.

    After some more research I discovered the results reference and have already changed that part of my formula but another forum post instructed me to use a COUNTDISTINCT formula for the 3rd part of the LOOKUP and that seems to be what I need since all I need is a count of every entry instead of having a column to reference for quantity. I was arbitrarily using A:A since I believed I had to use the GROUPBY function and have since changed the formula to this:

    This formula seems to give me the exact output I need as well: 

    That is 2,5 and 2 for June, July and August respectively. There is also one for March 2022 that is not showing in my snip. However, as you can see from my chart above, those three months are summing into March 2021 and the March 2022 value is in April 2021 (at least that is my assumption) so I'm not sure what I'm doing wrong.

  • 0
    Avatar
    James Locklear

    For anyone reading this with the same problem, my last solution did actually work. I guess I just need to reload the page or something because when I came into work today everything worked as intended.

Please sign in to leave a comment.