Filtered Dashboard 2 tables through another table

[deltai]

Hello everyone,

I need help and guidance.
I have a table containing the main information.
The second table only has the months of the year for statistical purposes (counting the values ​​from the main table).
In this table I need to count these values ​​per month, however, with a third table performing the filter.
The count of all values ​​per month is working, but I can't create a filter for the year.
I've tried several ways:
Directly on the field or with Slice, but nothing works.
See....

This table contais month to statistics:

And this are filter

On table BD_ESTATISTICA are 2 fields [QTD_PROC_TOTAIS_MES_ATIVOS] that on view is TOTAL ATIVOS MÊS and [QTD_PROC_TOTAIS_MES] that on view is TOTAL MÊS.

App formula on field [QTD_PROC_TOTAIS_MES]

IFS([_THISROW].[MES] = "TOTAL",
       COUNT(BD_PROC_JUDICIAL[N_PROCESSO]),

[_THISROW].[MES] = "JANEIRO",
    COUNT(SELECT(BD_PROC_JUDICIAL[N_PROCESSO],
                                  [MES] = TEXT([DATA_EXPEDICAO], "MMMM"))),

[_THISROW].[MES] = "FEVEREIRO",
   COUNT(SELECT(BD_PROC_JUDICIAL[N_PROCESSO],
                                  [_THISROW].[MES] = TEXT([DATA_EXPEDICAO], "MMMM"))),

[_THISROW].[MES] = "MARÇO",
    COUNT(SELECT(BD_PROC_JUDICIAL[N_PROCESSO],
                                  [_THISROW].[MES] = TEXT([DATA_EXPEDICAO], "MMMM"))),

[_THISROW].[MES] = "ABRIL",
   COUNT(SELECT(BD_PROC_JUDICIAL[N_PROCESSO],
                                 [_THISROW].[MES] = TEXT([DATA_EXPEDICAO], "MMMM"))),

[_THISROW].[MES] = "MAIO",
   COUNT(SELECT(BD_PROC_JUDICIAL[N_PROCESSO],
                                 [_THISROW].[MES] = TEXT([DATA_EXPEDICAO], "MMMM"))),

[_THISROW].[MES] = "JUNHO",
   COUNT(SELECT(BD_PROC_JUDICIAL[N_PROCESSO],
                                 [_THISROW].[MES] = TEXT([DATA_EXPEDICAO], "MMMM"))),

[_THISROW].[MES] = "JULHO",
   COUNT(SELECT(BD_PROC_JUDICIAL[N_PROCESSO],
                                  [_THISROW].[MES] = TEXT([DATA_EXPEDICAO], "MMMM"))),

[_THISROW].[MES] = "AGOSTO",
   COUNT(SELECT(BD_PROC_JUDICIAL[N_PROCESSO],
                                 [_THISROW].[MES] = TEXT([DATA_EXPEDICAO], "MMMM"))),

[_THISROW].[MES] = "SETEMBRO",
   COUNT(SELECT(BD_PROC_JUDICIAL[N_PROCESSO],
                                 [_THISROW].[MES] = TEXT([DATA_EXPEDICAO], "MMMM"))),

[_THISROW].[MES] = "OUTUBRO",
   COUNT(SELECT(BD_PROC_JUDICIAL[N_PROCESSO],
                                  [_THISROW].[MES] = TEXT([DATA_EXPEDICAO], "MMMM"))),

[_THISROW].[MES] = "NOVEMBRO",
   COUNT(SELECT(BD_PROC_JUDICIAL[N_PROCESSO],
                                 [_THISROW].[MES] = TEXT([DATA_EXPEDICAO], "MMMM"))),

[_THISROW].[MES] = "DEZEMBRO",
   COUNT(SELECT(BD_PROC_JUDICIAL[N_PROCESSO],
                                  [_THISROW].[MES] = TEXT([DATA_EXPEDICAO], "MMMM")))
)

[Steve]

The problem here is that the month table needs to be told that the filter has been updated to prompt it to recompute the values. When you change the year, that update needs to go to the server, the server will recompute the stats and send the stats back. There is no way to avoid this delay given your current setup.

[deltai]

Then...

This is where my problem lies, as I couldn't understand the formula very well.
I have a Table called BD_FILTRO[FILTRO_ANO] and with this field I want to filter.
It is of type ENUM and gets the years to be reported with Valid If:

 SELECT(BD_PROC_JUDICIAL[ANO_PROCESSO], TRUE, TRUE)

However, using your formula and changing it a little (if I understand correctly), it presents an error: Cannot compare List with Text in (BD_FILTRO[FILTRO_ANO] = TEXT([DATA_EXPEDICAO],"yyyy"))

The formula looked like this:

IF(
    ("TOTAL" = [MES]),
    COUNT(BD_PROC_JUDICIAL[N_PROCESSO]),
    COUNT(
       SELECT(BD_PROC_JUDICIAL[N_PROCESSO],
                       AND(
                                  ([_THISROW].[MES] = TEXT([DATA_EXPEDICAO], "MMMM")),
                                  (BD_FILTRO[FILTRO_ANO] = TEXT([DATA_EXPEDICAO], "yyyy"))
                                )
                    )
                )
  )

I know I only changed the FALSE IF sentence, just to show you if I'm on the right path?

[Steve]

Try changing this:

(BD_FILTRO[FILTRO_ANO] = TEXT([DATA_EXPEDICAO], "yyyy"))

To this:

IN(TEXT([DATA_EXPEDICAO], "yyyy"), BD_FILTRO[FILTRO_ANO])

[deltai]

Fantastic friend!!

That was exactly what I needed.

Just one situation:
When applying the filter, the table does not change automatically, but only after clicking the AppSheet update button.

Is there anything I can do about this?

[Steve]

That's what I was referring to in my very first response, above.

[deltai]

Ah ok. So is it necessary to create slice to solve this problem.

[Steve]

Yes. If, instead of maintaining a table with only one month's stats, you have all twelve months', then you could use a slice to display only the selected month.

[deltai]

I understand.
However, the view as it is presented meets my needs, as I only need to change the year of the research.
But another question that has now arisen: Which of the tables will I do the Slice on?

Could you guide me on how to build the formula?

[deltai]

May you help me?

[Steve]

Your App formula for QTD_PROC_TOTAIS_MES can be expressed more simply:

IF(
  ("TOTAL" = [MES]),
  COUNT(BD_PROC_JUDICIAL[N_PROCESSO]),
  COUNT(
    SELECT(
      BD_PROC_JUDICIAL[N_PROCESSO],
      ([_THISROW].[MES] = TEXT([DATA_EXPEDICAO], "MMMM"))
    )
  )
)

[deltai]

Thanks,

I really didn't see this form.