Unknown_fetch_failure -- unable to add filter with calculated field

Q&A
, ,
1

Hi team, i am facing an error message: "UNKNOWN FETCH FAILURE: Unable to fetch filter values. Refresh later or use the “Custom filter list”. Appreciate your advice someone on how to fix my calculations. thanks!

I have a calculated field, this is to calculate total number of students who will be attending classes starting in the next 12 months. I have used ifelse statement because i wanted to convert NULL value to 0 in the table visual (with 0 value, i can apply conditional formatting in table.)


    ifelse(
    isNull(

sumIf({students},
    truncDate('DD',{xEnd date})>truncDate('DD',now()) AND
    truncDate('DD',{xEnd date})<=addDateTime(12,'MM',truncDate('DD',now())))
            ),0,

sumIf({students},
    truncDate('DD',{xEnd date})>truncDate('DD',now()) AND
    truncDate('DD',{xEnd date})<=addDateTime(12,'MM',truncDate('DD',now())))
  1. referencing the above, i created a 2nd calculated field, to group the number of student count. For example,
    ifelse(
{students_count_next_12M}< 100,'Less than 100',
'100 and above')

When i try to add 2nd field’s filter into the sheet, i get the error message.

In addition, the 2nd field cannot be added under “Group By” field wells even though the values are text. it can only be added to “Value” field wells.

2

Hi @imevil,
What if you tried to use a coalesce function instead, it would look something like:

coalesce(
ifelse(
truncDate('DD',{xEnd date})>truncDate('DD',now()) AND
truncDate('DD',{xEnd date})<=addDateTime(12,'MM',truncDate('DD',now())),
{students},
0), 0)

Then, once added to field well, you can change the aggregation to ‘sum’ from there.

For the grouping calculation, let’s try this instead:

ifelse(sumOver({students_count_next_12M}, [], PRE_AGG) > 100, 'Less than 100', '100 and Above')

Last thing you will need to check is if there is a partition for the students count. Is this all students across the board or is it by group, like school or district? That may alter the final grouping metric calculation.

Let me know how this works for your case and if you have any additional questions!

3

Hi @imevil,
It’s been awhile since we last heard from you; following up to see if you had any additional questions regarding your post?

If we do not hear back within the next 3 business days, I’ll go ahead and close out this topic.

Thank you!

4

Hi @imevil,
Since we haven’t heard back, I’ll close out this topic. However, if you have any additional questions, feel free to create a new post in the community and link this discussion for relevant information if needed.

Thank you!