Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Oddity on Values for Report Using Date Table

I have the following set of data, just one of many rows of a similar nature but in this instance for January 2020, showing the number of falls in a building. There are three such tables, splitting th...
  • Anonymous's avatar
    Anonymous
    6 years ago

    I've figured it out myself, and in retrospect I should've realised with those dates later in the year. My DateDone is in UK date format, whereas for some reason it was converting the new column into US format. Odd though, as the forumula for Date was dd/mm/yyyy, and not mm/dd/yyyy:

     

    Anyway, I created another new column called NewDateNAI (for the NAI table) but to more closely match my Calendar date table:

     

     

    I then noticed, where I hadn't before (and should have...) that DateDone was ie 02/06/2020, but my first Date column based on it was showing 06 February 2020. Obviously this is wrong. Once I corrected this issue, my graph has started showing correct values in the corresponding months, and there are no longer any entries for Sept - Dec. 

     

    Just for something to be aware of here, as I would've thought "dd/mm/yyyy" would stipulate UK date format, but it did not. I'm unsure why. 

     

     

     

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago
I haven't put all the tables together because they aren't just a sum and there aren't any identifiers within that data that says "this is severity 1", "this is severity 2". Well, there are, but I've already performed that process and don't want to change things like that. I've added some of the data, but you will see that none of it goes between Jan 2020 and Aug 2020.
Anonymous's avatar
Anonymous
Not applicable
6 years ago

I've figured it out myself, and in retrospect I should've realised with those dates later in the year. My DateDone is in UK date format, whereas for some reason it was converting the new column into US format. Odd though, as the forumula for Date was dd/mm/yyyy, and not mm/dd/yyyy:

 

Anyway, I created another new column called NewDateNAI (for the NAI table) but to more closely match my Calendar date table:

 

 

I then noticed, where I hadn't before (and should have...) that DateDone was ie 02/06/2020, but my first Date column based on it was showing 06 February 2020. Obviously this is wrong. Once I corrected this issue, my graph has started showing correct values in the corresponding months, and there are no longer any entries for Sept - Dec. 

 

Just for something to be aware of here, as I would've thought "dd/mm/yyyy" would stipulate UK date format, but it did not. I'm unsure why.