Post

2 followers Follow
0
Avatar

Calculating number of items between dates based on specific category

I'm trying to set up a calculation field to count the number of items where a specific category has been selected, within a date range. I can count the total for the year, but not break it down by month, and I'm guessing it's something to do with my IF statement including two variables.

 

This works (year to date):

 

var cat = @All of Authorised absence type;
var sum = 0;

for (var i = 0; i < cat.length; i++) {
if (cat[i] == "Holiday") {
sum += 1;
}
}
sum

 

BUT I need to break it down to show holidays taken in each month. This is what I've got, and it doesn't work:

 

var cat = @All of Authorised absence type;
var dates = @All of TimetableDate;
var month = "December 2019";
var sum = 0;

for (var i = 0; i < cat.length; i++) {
var date = moment(dates[i]).format("MMMM YYYY");
if ((month == date) && (cat[i] == "Holiday")) {
sum += Number(cat[i]) || 0;
}
}
sum

For example, I would expect to see 0 for September, but 3 in December (I will be setting up the field for each month). At the moment the preview shows 0, as does the field in the app. However, if I type cat.length at the end of the calculation the preview shows 1.

Any help that can be given would be great!

Susan Warren

Please sign in to leave a comment.

8 comments

0
Avatar

Hi Susan,

this is wrong: 

sum += Number(cat[i]) || 0;

cat[i] is e.g the word "Holiday" - and that can't be parsed to a number. You would get the error NaN = Not a number - and because of NaN the || 0  parses it to 0. So you sum will always be 0

Simply use 

sum += 1;

as in your other calulation.

Rainer

 

Rainer Grabowski 0 votes
Comment actions Permalink
0
Avatar

Thanks Rainer, that works perfectly.

I have a related issue in that I need to count the number of items with a different setting from the same category field - where I was counting Holiday, I now need to count Sickness. Part of the problem is that I have two different categories of sickness which can be used, but I want to total both types.

For year to date, this works:

var acat = @All of Authorised absence type;
var sum = 0;

for (var i = 0; i < acat.length; i++) {
if ((acat[i] == "Illness - only authorised by senior managers") || (acat[i] == "Young Person unwell and informed bemix/employer")) {
sum += 1;
}
}
sum

Separating out by month does not.

So far my code is:

var acat = @All of Authorised absence type;
var dates = @All of TimetableDate;
var month = "September 2019";
var sum = 0;

for (var i = 0; i < acat.length; i++) {
var date = moment(dates[i]).format("MMMM YYYY");
if ((month == date) && (acat[i] == "Illness - only authorised by senior managers") || (acat[i] == "Young Person unwell and informed bemix/employer")) {
sum += 1;
}
}
sum

However - this is giving me the full year's worth of data and not looking at the month - e.g. there might be 10 entries for the year, but September should only have 2.

Please can you point me at where I've gone wrong?

Susan Warren 0 votes
Comment actions Permalink
0
Avatar

Hi Rainer

I've just spotted a major error in the report for Holidays using the code above. The total for the year is correct, but the monthly breakdown is wrong.

In the above report, you can see the yearly total (which is correct) and a breakdown per month as per the code on this post. However, the breakdown is giving the wrong information.

Of the ones in this example:

Row 4 shows 2 days in Sept, 1 in Oct. The timetable referenced in the code shows 1 in Sep and 2 in Dec.

Row 5 shows 2 in Nov, 1 in Dec, 1 in Jan. The timetable shows 3 in Dec, 1 in Jan.

Row 8 shows 1 in Oct, 1 in Nov, 2 in Dec. The timetable shows 4 in Dec.

And so on - every record which has holiday information (16 out of 31 records) is showing the wrong monthly breakdown. Help! We use this information for reporting to government so it is vital to have the right information.

Susan Warren 0 votes
Comment actions Permalink
0
Avatar

Hello Susan, 
in the IF you need extra brackets. This part must be wrapped in extra parentheses:

((acat[i] == "Illness - only authorised by senior managers") || (acat[i] == "Young Person unwell and informed bemix/employer"))

So whole If must look like this: 

if ((month == date) && ((acat[i] == "Illness - only authorised by senior managers") || (acat[i] == "Young Person unwell and informed bemix/employer"))){

Also check whether both fields of all items you reference in the vars are filled. You can check it this way: Under your existing code enter
cat.length, remove it and enter dates.length - both must show the same number. If not you must use the tokens @all of ... with null from the dropdown list.

Rainer

Rainer Grabowski 0 votes
Comment actions Permalink
0
Avatar

Thank you Rainer - my totals are now set. However - I'm having the same error in the sickness as I am getting with the holidays, in that the monthly totals are wrong.

The overall number for the year is correct, but the monthly breakdowns are wrong. Screenshot:

 

Line 3 - a total of 5 authorised sickness, the report shows 2 in Sept and 3 in Oct. However, the timetable shows 1 in Oct, 3 in Nov, 1 in Dec.

I'm getting the same issue for all students, just as I have the wrong information in the monthly breakdown of holidays.

I cannot think what is causing this. The code is supposed to match the timetable date to the given month! Any idea why it's going wrong?

Thanks

Su

 

Susan Warren 0 votes
Comment actions Permalink
0
Avatar

Hi Rainer, screenshot as requested for both holiday and authorised sickness - as both are coming up with the wrong info. The other months have the same calculations, other than "var month" being each month in turn (October, November, etc).

Thanks.

Susan Warren 0 votes
Comment actions Permalink
0
Avatar

Bumping for attention - is there any way to resolve this error? I can't see why the code would give the wrong information on the monthly breakdown.

Susan Warren 0 votes
Comment actions Permalink