How to Filter Case Data by Date and Village in a Form Calculation

Users
1

I am working on a CommCare application where I need to calculate summary data within a form. Specifically, I want to:

  1. Count the total number of participants(total registers) in a selected village.
  2. Count how many participants have `treatment = 'yes' for today and the previous day.

I have used the following XPath formula to count cases:

count(instance('casedb')/casedb/case[
village = #form/village and
treatment = 'yes' and
@status = 'open' and
(
format-date(submission_date, '%Y-%m-%d') = today() or
format-date(submission_date, '%Y-%m-%d') = date(today() - 1)
)
])

Unfortunately, it doesn't work when i use date function. Could you pls help me with this?

2

Hello,

The issue you're facing with the date function in your XPath expression is likely due to the way dates are being formatted and compared. In CommCare, date calculations can be tricky, especially when using functions like today() and date(). Here's a revised approach to help you achieve your goals:

  1. Count the total number of participants in a selected village:

    count(instance('casedb')/casedb/case[
    village = #form/village and
    @status = 'open'
    ])

  2. Count how many participants have treatment = 'yes' for today and the previous day:

Instead of using format-date, you can use the date() function directly with today() to calculate the date for today and yesterday. However, ensure that the submission_date is stored in a comparable format (e.g., yyyy-mm-dd). Here's a revised expression:

count(instance('casedb')/casedb/case[
village = #form/village and
treatment = 'yes' and
@status = 'open' and
(
submission_date = today() or
submission_date = date(today() - 1)
)
])

Make sure that submission_date is stored in a format that matches the output of today() and date() functions. If the date format is different, you might need to adjust the format or use additional functions to ensure compatibility.

Here is the link to public documentation which can be helpful in this regard: https://dimagi.atlassian.net/wiki/spaces/commcarepublic/pages/2143956726/Child+Cases#[Advanced]-Linking-Parent-and-Child-Case-Data

Let me know in case you need any further help.

Best,
Shiv

1 Like