Looking to create a Klip that only pulls data from the current month - I have attached an of an example of the data set.
What Formula would I need to use to only COUNT cells which have a date in the current month?
Date
Votes
3 comments
-
Hi Tom,
To get all the dates in column C that are in the current month you can use DATE_IN( DATE( @C:C,"M/d/yy"),month). This gives you a set of true/false values.
You can combine this with other logic to get the count. For example if you are building a label component you could add a filter to filter on TRUE and then "show aggregated value" will count the results.
Another option is to use the COUNTIF function. e.g. COUNTIF(DATE_IN( DATE( @C:C,"M/d/yy"),month))
Here are some links to the function references and filtering docs in our knowledge base.
https://support.klipfolio.com/hc/en-us/articles/360011416893-Klipfolio-functions?query=functions#DATE_INhttps://support.klipfolio.com/hc/en-us/articles/222017088-Using-the-Filter-Group-Aggregate-and-Sort-actions-videos-
https://support.klipfolio.com/hc/en-us/articles/115004538487-How-do-I-filter-data-Hope this helps,
Keely
-
Thank you Keely! Very helpful.
I've tried to go one step further by building a formula that shows a value ONLY IF column B & column C are both in the current month.
COUNTIF(DATE_IN(DATE(@DOC,B:B;,"M/d/yy"),"month"),AND(DATE_IN(DATE(@DOC,C:C;,"M/d/yy"),"month")))
Doesn't seem to be working? What have I done wrong here?
-
Hi Tom,
You have the right idea. The syntax for the AND needs to be adjusted just a bit.
Try COUNTIF(
AND(
DATE_IN(DATE(@DOC,B:B;,"M/d/yy"),"month"),DATE_IN(DATE(@DOC,C:C;,"M/d/yy"),"month")))
And for future reference - if you want to get data if either of the columns are in the current month you can use OR instead of AND.
Keely
Please sign in to leave a comment.