Calculated field using parameter not works as expected

Question & Answer
, , , ,
1

Hello,
I want to compare the number of flights between two months in my dataset.
I have this calculated field:


distinct_count(
    ifelse(
        extract("YYYY", when) = extract("YYYY", truncDate("MM", addDateTime(${SelectedMonthOffset}, 'MM', now()))) AND 
        extract("MM", when) = extract("MM", addDateTime(${SelectedMonthOffset}, 'MM', now())) AND 
        extract("DD", when) >= 1 AND 
        extract("DD", when) <= 31 AND 
        duration > 0, 
        {mission_id},
        NULL
    )
)

Which works great. When I set the parameter to -1 or -2, it shows me the corresponding flights in those months.

Now, I want the same calculated field for the month before the one I set in the parameter.
I tried this:


distinct_count(
    ifelse(
        extract("YYYY", when) = extract("YYYY", truncDate("MM", addDateTime(${SelectedMonthOffset} - 1, 'MM', now()))) AND 
        extract("MM", when) = extract("MM", addDateTime(${SelectedMonthOffset} - 1, 'MM', now())) AND 
        extract("DD", when) >= 1 AND 
        extract("DD", when) <= 31 AND 
        duration > 0, 
        {mission_id},
        NULL
    )
)

But it’s not working. (When I set the parameter to -2, it works in the first calculation; that’s not the problem. The second one is not working for any number, including 0.)
It lets me save the calculated field, but it doesn’t show it on the graph.

image
image1088×265 3.44 KB

Any idea what is the problem?

Thanks,
Maya

2

Hello @maya_k, hope this message finds you well!!

If I understand well you problem, it seems like the issue with your calculated field for the previous month might be related to the logic used in the addDateTime and truncDate functions. Ensure that the date calculations are correctly adjusting the month offset. Here’s a refined version of your formula:

distinct_count(
    ifelse(
        extract("YYYY", when) = extract("YYYY", truncDate("MM", addDateTime(${SelectedMonthOffset} - 1, 'MM', now()))) 
        AND extract("MM", when) = extract("MM", truncDate("MM", addDateTime(${SelectedMonthOffset} - 1, 'MM', now()))) 
        AND extract("DD", when) >= 1 
        AND extract("DD", when) <= 31 
        AND duration > 0, 
        {mission_id}, 
        NULL
    )
)

Make sure the parameter ${SelectedMonthOffset} is correctly set and interpreted. Also, verify that the calculated field is properly added to your visualisation. If the issue persists, try using fixed values for debugging to isolate the problem. If everything seems correct, check for any dataset constraints or filters that might be affecting the display.

Please, tell me if it works for you

3

Thanks for the respond,
It still not work.

when I put -2 instead of my parameter it works
when I put -1-1 its not working
when I put only my parameter without -1, and set it to -2 it works also

4

Hello @maya_k

Are you still working on this or were you able to find a solution?

If you are still trying to troubleshoot this issue, I would try pre-calculating your addDatetime - N portion.