0

Table Needs to Return Grouped SUM from User Input (Variables)

Hello,

I'm trying to have a table return certain summed up values based on a date ranged, which is entered by a user in a seprate Klip.

 

Unfortunately, I have not been able to figure out how to accomplish this, and keep getting system errors. \

Below is my formula and a screenshot of my table as well. 

My user input comes from a table of dates in the following format "MMMM-yy", and the dates listed in the table I'm working on are in the "d-MMMM-yy" format, which I've added a DATE_CONVERT thinking it would fix it. But it did nothing. 

 

SELECT(SUM(Goal_Quantity,BETWEEN(GROUPBY(DATE_CONVERT(Goal_Date,"d-MMMM-yy","MMMM-yy"),Goal_Date),$dateRangeStart,$dateRangeEnd)))

 

1 comment

  • 0
    Avatar
    Meggan King

    Hello,

    What are the dates for the dateRangeStart and dateRangeEnd? Do they include the day? Are they the full month of MMMM or just a partial MMM? Without seeing how the dates look, it's possible there is no date match. 

    With SELECT, you would also do this on the data before SUM or GROUPBY. Right now, the SUM and GROUPBY are not in the right places, so this formula is incorrect. You must Select the specific data for the function of SUM or GROUPBY. GROUPBY will also require 2 columns in it's parameters. You can read more here: https://support.klipfolio.com/hc/en-us/articles/215547998-GROUP-GROUPBY-and-COUNTDISTINCT-video-

    It will be easier to assist if you open a support ticket with the Klipfolio team. They can then help you work on your specific formula. They are multiple ways to build and it might be easier to use Applied actions instead of functions and formulas. If you choose to use functions and formulas, you must apply the same logic in every column of data vs applying the logic to the table at once. 

     

    Hope this helps,

    Meggan

Please sign in to leave a comment.