We have noticed a funny experience with our dhis2 instance v2.37.10, Build revision 2d0f892. We are currently conducting an ITN distribution campaign, which started early 2023. For this period we have been capturing this tracker data. We are noticing that data for 2023 capture in 2023 is not populating, but test data for 2022 and data capture in 2024 is available.
When we pick anything related to 2023 period type no data in visualizer, linelist etc. We have dropped analytics run them time and again but nothing seems to change this situation.
I would suggest to get list of events for the 2023 and analyse the event and enrollment date.
If you can get there data, then check the analytics period boundaries, offsets or date field used for the calculations.
If you found invalid dates that will be the root of problems, why you canât get any numbers. So speak to the people who collected data. Additionally check the date settings which used on the time of data collection and current settings.
I managed to clean the enrollmentDates which were wrong and well as the dueDate. I have put the offset to keyAnalyticsPeriodYearsOffse = 5 but analytics are now failing completely with error ERROR: date/time field value out of range: "0000-11-30". Any other ideas?
@Gassim, are you able to arrange a meeting with any of the dev person dealing with analytics. I have exhusted all my efforts.
@moses_mwale why you put keyAnalyticsPeriodYearsOffse = 5?
What was the reason? Initially you have been talking about the data which you cannot get for the year 2023
Also question : how did you clean enrollment dates? What that means?
Additionally as I can see now you have year 0000, which I believe cannot be processed by DHIS2 and could be taken as an exceptionâŚ
Hope you have made backup before you âcleanedâ the enrollment dates
This is a development instance, production is still untouched. The default offset is -1, which also still give same error on v2.39.4 when doing analytics. the 0000 is no where present in my database, this is a bug.
On v2.37 analytics are running but still no data even after cleaning.
How did I clean wrong enrollment, I copied created Date into enrollmentdate.
How do you suggest I do the data cleaning for the dueDate and the enrollmentDate?
UPDATE programinstance
SET enrollmentdate = created
where (EXTRACT(year from enrollmentdate) < 2022 or EXTRACT(year from enrollmentdate) > 2029);
UPDATE programstageinstance
SET executiondate = created
where (EXTRACT(year from executiondate) < 2023 or EXTRACT(year from executiondate) > 2029);
UPDATE programstageinstance
SET duedate = executiondate
where (EXTRACT(year from duedate) < 2023 or EXTRACT(year from duedate) > 2029);
For the offset I was trying to see what will happen or at all something will change, but I have already set it to default (-1).
Have you checked your TEIsâ dates after the update? Actually question: are you trying to count Enrollments or Events? and do you use relevant date field?
Thank you for your support my friend. I managed to find the solution. The problem was an invalid attribute Value or values from the attribute age value type Age. When capturing demographic data, there is a possibility that the default date value (0000-00-00) from the date picker is saved if users havenât checked or verified what they are capturing. When processing analytics, the constraint for that field will not accept the invalid date thus skipping the period (2023, etc) where this date originates from.
I should suggest to the developers to put constraint to this value type Age, not to accept anything less than 1900 in years maybe.
I had ages like 0000-11-03, 0100-12-10, etc this can not be accepted in the analytics tables.
Anyone else who will face this challenge when using v2.37 or lower, am afraid, you will not be able to be shown any errors in the logs or red flags but the data will be missing. V2.38 or higher will flag the errors when analytics fail but you will not be able to know which table is causing the problem. It requires serious debugging.
Resolution
Note: v2.37 will not show errors when running analytics for some reason will will skip the problematic period and do a successful analytics table run.
Upgrade to v2.38 or higher to see analytics errors. In my case I noticed the following error - ERROR: date/time field value out of range: "0000-11-30".
Search for this period in the tables that accepts dates e.g ProgramInstance, ProgramStageInstance, ProgramStage, trackedentityattributevalue (If you have age or DOB value type), etc. Alternatively, search the entire database text using vim, nano, less or any other powerful text editor - (Unzip the database to text). My problematic table was trackedEntityAttributeValue. Attribute Age with value type age was pushing wrong dates beyond the valid dates.
@moses_mwale Hi Moses! Any chance we could connect to know more about the campaign work you are working on? I would be very curious to know more about your implementation, workflow, and solutions!
We are currently using v2.40.1 and we been having an error- `ERROR: date/time field value out of range: â0000-06-10â. We managed to find the dates out of range in table trackedentitydatavalueaudit after searching from all tables mentioned and the managed to fix the dates and our analytics run successfully.
