Search code examples
klipfolio

Select data based on a date range and Item number

I am new to Klipfolio and need some help selecting data into a Klip column. From the below screenshot, I need to be able to select the total amount of receipts (Column L:L) entered per item ID (Column H:H) that occured in the date range between TODAY()-10 and TODAY()-4 (Column C:C). This is what has been developed thus far:

LOOKUP(@Sheet1,G:G;, --This is a lookup column in the Data Source 'O&M Weekly Allocation Rpt'
group(select(@sheet1,A:A;,BETWEEN(DATE(@C:C;,"MM/dd/yyyy"),DATE_ADD(TODAY()-10),TODAY()-4))),
GROUPBY(select(@sheet1,H:H;,BETWEEN(DATE(@sheet1,C:C;,"MM/dd/yyyy"),DATE_ADD(TODAY()-10),TODAY()-4))),
SELECT(@sheet1,L:L;,BETWEEN(DATE(@sheet1,C:C;,"MM/dd/yyyy"),DATE_ADD(TODAY()-10),TODAY()-4)))

However this is some kind of an error (bad syntax would be my guess). Any help would be appreciated! Thanks.

enter image description here

enter image description here

Solution

  • DATE_ADD() has a required second parameter that is the date period which you are missing. The period in your case is "day" which will need to be selected from a drop-down once you insert a comma to include the second parameter:

    DATE_ADD(TODAY(),day,-10)

    To return data between 10 days and go to 4 days ago it would look like:

    BETWEEN(DATE(@sheet1,C:C;,"M/d/yy"),DATE_ADD(TODAY(),day,-10),DATE_ADD(TODAY(),day,-4))

    Additionally, it appears the dates in your raw data are "M/d/yy" as in 5/11/20 and not "MM/dd/yyyy" which would expect 05/11/2020.