Calculating number of items between dates based on specific category

Comments

13 comments

  • Rainer Grabowski

    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

     

    0
    Comment actions Permalink
  • Susan Warren-Moy

    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?

    0
    Comment actions Permalink
  • Susan Warren-Moy

    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.

    0
    Comment actions Permalink
  • Rainer Grabowski

    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

    0
    Comment actions Permalink
  • Susan Warren-Moy

    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

     

    0
    Comment actions Permalink
  • Rainer Grabowski

    Susan Warren-Moy Can you please share screenshots of the calculation?
    Rainer

    0
    Comment actions Permalink
  • Susan Warren-Moy

    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.

    0
    Comment actions Permalink
  • Susan Warren-Moy

    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.

    0
    Comment actions Permalink
  • Susan Warren-Moy

    Hi Rainer - and anyone else who could help

    I'm still having issues with this. It's a major problem if our reports can't be relied on for a monthly breakdown of holiday or sickness, because this section of our company operates as a school, and as such we have to report to the government on holiday / sickness as part of the students attendance.

    I have not made any changes to the code since this was first flagged as I don't know what I could change to make each month pick up properly. Maybe the order in which the IF statement runs? (so that it picks up the category of "holiday" or "sickness" before checking the date range?) That's about the only thing I can think of which might change things.

    0
    Comment actions Permalink
  • Rainer Grabowski

    Hey Susan, 

    it's hard to say what's going wrong without knowing the involved apps/fields. 
    Are the category field and the date field in the referenced app mandatory/required fields (in other words: is it sure that these two fields are filled in each related item)?
    The code in the screenshot looks ok and the order of the IFs doesn't matter.

    What exactly doesn't work correctly? Did it ever work correctly?

    Rainer 

    0
    Comment actions Permalink
  • Susan Warren-Moy

    Hi Rainer

    The date field is mandatory, but the absence type category field is not (because it will only be set if someone is absent, not if they attend).

    What works correctly: the total figures for the full year (pulling the category type without a date range).

    What does not work correctly: the monthly figures (pulling the category type if the date matches the variable). This has never worked correctly. There is no accuracy in filtering the category type by a monthly range.

    The app that this information is drawn from works fine - if you set a filter up and then run a report in that app, you get the correct results.

    I hope that helps clarify things - it can be hard for me to describe this properly!

    Su

    0
    Comment actions Permalink
  • Rainer Grabowski

    Hi Susan,

    but the absence type category field is not 

    That's the reason why it doesn't work. When you reference a field in the other app with @all of ... 
    you get those items only that have a value in that field. Means in your case: 
    If there are 100 items in the Absence app but in only 80 items the category is selected you get those 80 items only with @All of Authorized absence type. But because the date field is mandatory you get all 100 items with @all of Timetable date. 

    Let's say item 1 has both values: category selected and date. Item 2 has no category selected, item 3 has a category selcted. 
    In the array var acat the "empty" (value = null)  item 2 is missing, item 3 becomes item 2 in that array. But in the other array var dates item 2 is the one without a category, but a date 

    The loop for(i = 0; i < acat.length; i++) now looks if acat item 1 matches the if conditions and takes the date from dates item 1 for the counting. Then it takes acat item 2 (which was originally item 3) and takes dates item 2 for the counting - but dates item 2 has an empty category (= null) and it isn't the same item as in the array acat.

    In a for loop with 2 arrays both arrays should contain the same number of items, otherwise you get a wrong result. You can check the number of items in the arrays by entering acat.length as last line, the result is a number. Then enter dates.length - and I'm sure the number is not equal to acat.length.

    The solution for you is using the field token "with nulls" for var acat: 

    var acat = @All of Authorised absence type with nulls;
    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

    The tokens "with null" you find at the end of the list that pops up when you enter @all of ... 

    Rainer
     

    0
    Comment actions Permalink
  • Susan Warren-Moy

    Ah, Rainer, thank you! That's perfect - I knew it had to be something I was overlooking - hadn't thought about the need for using "with nulls".

    I've set it up and so far all looks good. You're a superstar, thank you!

    0
    Comment actions Permalink

Please sign in to leave a comment.

Powered by Zendesk