0

Sum of column filtered by two columns including a date range

Hi there,

I'm struggling to get a simple equivalent of - what would be in sql = count all where collumn1 = x and column2-date = year 2021

I am using a SUMIF ands it looks like this:

SUMIF((AND ((0f6969c8d5deefae2f3709fad0ebf452#TaskID;="120730"),(BETWEEN(0f6969c8d5deefae2f3709fad0ebf452#TimeStamp;, "2021-01-24T10:11:28Z", "2021-01-24T10:11:28Z"))), 0f6969c8d5deefae2f3709fad0ebf452#Total treated;) )

 

I've tried other approaches too - but I can't the right result or stop the dreaded "The formula returned an error." message.

In summary, my mission is to:

Sum column numbers of @Total treated where the @TimeStamp column is the year 2021 and the @TaskID column ="120730"

If anyone could help it would be massively appreciated.

Kind regards, Steve. 

 

2 comments

  • 0
    Avatar
    Meggan King

    Hi Steve,

    BETWEEN using UNIX dates or numbers, so you could convert your date options to UNIX, or you could use DATE_IN instead. 

    https://support.klipfolio.com/hc/en-us/articles/360011416893-Klipfolio-functions#BETWEEN

    https://support.klipfolio.com/hc/en-us/articles/360011416893-Klipfolio-functions#DATE_IN

    Example for DATE_IN: DATE_IN(DATE(@TimeStamp,"yyyy-MM-dd'T'HH:mm:ss"), year) This would be for current year. 

     

    Hope this helps!

     

  • 0
    Avatar
    Taskdotio Ltd admin

    Brilliant, thanks Meggan!

    I also got a reply from Parker in support, so I'll share this info too in case there's extra detail that helps other people:

    Looking at your post, your formula would be failing because BETWEEN requires dates in UNIX format in order to return properly. 

    Beyond that though, a DATERANGE and IN formula might be a smoother way to accomplish this if you do not want dynamic date ranges. If you do want dynamic date ranges, a DATE_IN formula would be better suited. 

    This would essentially look like:

    IN(DATE(@Timestamp, "yyyy-MM-dd HH:mm:ss "),DATERANGE("1/1/2021","12/31/2021")) for exactly 2021
     
    or

    DATE_IN(DATE(@TimeStamp,"yyyy-MM-dd'T'HH:mm:ss"), year) for the current year

    Hope this helps! Let me know if you have any questions around this! 

     

    I have this now working great using this query:

    SUMIF(AND((0f6969c8d5deefae2f3709fad0ebf452#TaskID;="120730"),(DATE_IN(DATE(0f6969c8d5deefae2f3709fad0ebf452#TimeStamp;,"yyyy-MM-dd'T'HH:mm:ss"), "1")) ),0f6969c8d5deefae2f3709fad0ebf452#Total treated;)

    Many thanks for the help :)

Please sign in to leave a comment.