So, lets say i have 2 tables
table1 has columns [uniqueid](key), [Date of Entry](date), [Action] (a yes/no enum), [site](enum text)
table 1 has multiple entries per date with variable entries for action and site.
table 2 has columns [computedkey], [Date of Entry](date & label), [Minor actions](number)
table 2 has 1 unique entry per date.
used a count filter expression in table 2 for minor actions (formula field) to get the number of columns in table 1 with the same date of entry that also have [action]="yes", & not([site]="option1").
Count(
FILTER(
"Table1",
AND(
[Date of Entry]= [_THISROW].[Date of Entry],
[action]="Yes",
not([Site]="option1")
)
)
)
I get nothing for every column, which is obviously not correct.
I tried using the filter expression in a virtual column without the count and it tests as expected, and shows the reference rows in a nested table.
Even when i wrap it in a count it works great.
but then when I press save and verify, the numbers disappear, and if i go back to test it, I get nothing. BUT the nested table still shows the related rows.
I get the same problem if I use a select expression in stead of filter, and I cant think of another way to do what im after, namely counting the number of rows in table 1 with the same date of entry in table 2 that also have specified values in 2 of table 1's columns.
I've tried using a virtual column in a different table entirely just to test the expression on a specified date of entry, but its the same problem, works when I put the expression in and when testing, once I press save and verify, I get no rows on the test and zero on the count.
anyone know what im doing wrong here!?
is something wrong with the expression , where im putting it, my table somehow, or my excel sheet!?
LinkedIn
Twitter
