Count # of Related Items *and* filter by date
I know how to count the # of related items in a calculated field.
How do I count the # of related items and restrict the count by a particular date field in that item? For example, I only want to count those where the date is within the past 30 days.
Thanks!
-
Hi Tom,
the problem is not the filter, but the value for "today" (see this posting)
The calculation:
var date = @all of date field;
var dates30 = 0;
for(var i = 0; i < date.length; i++){
if(date[i] >= moment().subtract(30,'d')){;
++dates30;
}
};or:
var date = @all of date field;
var dates30 = [];
for(var i = 0; i < date.length; i++){
if(date[i] >= moment().subtract(30,'d')){;
dates30.push(date[i]);
}
};
dates30.lengthRainer
-
Hi Carlos,
you just have to modify the IF-condition:
var date = @all of date field;
var cat = @all of category field;
var dates30 = 0;
for(var i = 0; i < date.length; i++){
if(cat[i] = "XYZ" && date[i] >= moment().subtract(30,'d')){;
++dates30;
}
};For getting the last month of last quarter (which would be Sep for the current date) you need a field where the current date (Nov 7, 2016) is in. There are some tricks to achieve this
Rainer
rg@delos-consulting.com -
Hi Rainer,
We are finding inconsistencies calculate the # of related items of a particular category field and filter by date using the formula you provided. It is pulling data back that isn't the same information. It doesnt seem like the code is pulling the same item information..
cat[i] = "XYZ" && date[i] >= moment().subtract(30,'d')
This should pull back a record in the for loop that has been found where date[1] and then analyze does the cat[1] also match "XYZ". But it doesn't appear to be the case. In the code, it seems the array data is off as though it is pulling date[1] and cat[2].
Here is the code that I am usingvar date = @All of Date to Send;
var cat = @All of Text Status;
var leads = 0;
for(var i = 0; i < date.length; i++)
{
if( (cat[i] == "Already sent & received message with seller") &&
((moment(date[i]).format("MM/DD/YYYY")) == (moment(@Current Date).format("MM/DD/YYYY"))))
{
leads++;
}
};
@Current Date is a field I created in the app where this calculation is being done as per your other message on https://help.podio.com/hc/en-us/community/posts/204569657-Definite-solution-for-today-handling-in-calculated-fields-days-since-days-until-countdown-incl-possible-solution
-
Hi Jared,
I bet that not all related items have a value in both fields: Date and category.at minimum in one of the items one field is empty.
You can simply check that:
Enter under your calculation:
date.length - cat.length
If the result not 0 the code doesn't work cause.
Example: there are 4 items related, in item 1 the date is missing. Array date looks like this [D-item 2, D-item 3, D-item 4], array cat = [C-item 1, C-item 2, C-item 3, C-item 4]. The for-loop iterates over the array and compares now
D-item 2 - C-item1
D-item 3 - C-item 2
D-item 4 - C-item 3
The solution: Instead of the variable token @All of Date to Send; and @All of Text Status; select the tokens @All of Date to Send with nulls and @All of Text Status with nulls. They always have the same length. But with this "with nulls" token you have to change your code a bit - it must be [i][0] cause the array with nulls are returned as nested arrays:var date = @All of Date to Send with nulls;
var cat = @All of Text Status with nulls;
var leads = 0;
for(var i = 0; i < date.length; i++)
{
if( (cat[i][0] == "Already sent & received message with seller") &&
((moment(date[i][0]).format("MM/DD/YYYY")) == (moment(@Current Date).format("MM/DD/YYYY"))))
{
leads++;
}
};
leadsRainer
-
Hi Rainer,
You are correct. There were some records with the date not populated. I'm still getting off numbers compared to doing a view in the app with the same parameters. Is there code that will print all the items associated with the number value that is being derived. For example, if there is a text field called "KEY", is there a way to reference all of KEY so I can see what values the equation is "seeing" to compare it with those I see in the view of the referenced app?
Thanks,
Jared
-
Hi Jared,
you can do it in 2 ways:
First way:
var date = @All of Date to Send with nulls;
var cat = @All of Text Status with nulls;
var key = @All of text field with nulls;
var result = [];
for(var i = 0; i < date.length; i++){
result.push(text[i][0] + " | " + moment(date[i][0]).format("MM/DD/YYYY") + " | " + cat[i][0]);
};
result.join("\n")Result is a list with 3 value per line.
The second way:
Add a calculation field in the source app, name it e.g. "Check" and enter("000" + @unique id).substr(-4) + " | " + @text feild + " | " + moment(@Date to Send).format("MM/DD/YYYY") + " | " + @Text statusAnd then in teh calculation field in the other app:
@all of check.sort().join("\n")The result is a list, sorted by the items unique id (oldest item at the top). Sort the app table view in the source app by Created Oldest First and you can compare the calculated list and the table view (or a filtered view).
Rainer
-
Thank you again Rainer for all the help. It looks like the issue is around trying to pull date out of date/time fields to compare them.
( (moment(date[i][0]).format("MM/DD/YYYY")) == (moment(@Current Date).format("MM/DD/YYYY")))Your code allowed me to see a different date was being shown for an item than I knew it was. I went to the referenced app to that item and added to the moment js code for that field and indeed, it isn't interpretting as expected.
Is there a proper way to extract the MM/DD/YYYY from a datetime field?
-
It doesn't seem there isnt a consistent value for the date in the date field. I'm not wrapping my head around why I need the .tz for the incoming date field but not the date field in the calculations app.
You will see in the statistics app, I I put the .tz on the calculation, it converts to a different date. But as you pointed out, on the incoming referenced app, it shows correctly.
-
Cause you have no time value in Current date.
Just check it and enter moment(@Current Date).format("MM/DD/YYYY HH:mm") - it shows 04/21/217 00:00 = UTC time
Adding .tc("America/Chicago") will substract 5 hrs cause Chicago = UTC -5Now enter 00:00 in the field Current Date - w/o .tz you will get 04/20/2017 19:00, but with .tz you'll now get the right result 04/21/2017 00:00
Btw: Welcome in the confusing world of time calculations :)
-
How would you tweak this to count all related items with the date field equaling a specific month?
For example, if my app has a relationship field for all items created in the current year. Then, I would have a separate calculation field for each month that gives me a total of the related items that fall under that month.
January - 20
February - 8
March - 15
etc..
Please sign in to leave a comment.
Comments
14 comments