Help Needed with Filtered Count Expression

[Ryan_Wagner]

Hi all,

Can anyone clue me in on what I'm doing wrong with this expression? My goal was to get a percentage of an item marked "dry" (the "Action") out of the total number of inspections for that item.

Another way to say it: I'm trying divide a count of a filtered ref ("history2") into a total count of ref rows.

I'd appreciate anyone's insight! Formula works but gives me all kinds of strange numbers, none of which are remotely correct.

[Ryan_Wagner]

(And here's the current output, which is incorrect):

[Steve]

Fix for expression giving 0% or missing decimal pa... - Google Cloud Community

[Ryan_Wagner]

Thanks, Steve. I always value your contributions. 

I gave those fixes a try, but unfortunately it's performing the same way.  Not sure if there is more info I can provide. I assumed the problem was somewhere in my attempt to count a filtered reference and divide into another filtered reference...

[Ryan_Wagner]

For illustration, here's a "3 out of 4 times were dry" scenario that I want to give me a "75%" but is outputting 100%.

[TeeSee1]

Try

COUNT( FILTER("history2",[Action]="Dry") )
/
COUNT( FILTER("history2",ISNOTBLANK([Action]) )

without [_THISROW].

[Ryan_Wagner]

Thank you. That seems to have gotten me closer? At least I'm getting different numbers now, but the math is still not working out...This one in the screenshot should be another "75%", but is showing as 66%...

This is strange.

[Ryan_Wagner]

This one should be 100%...

[Suvrutt_Gurjar]

Please give this a try by using the hint already give by @Steve in his reply

COUNT( FILTER("history2",[Action]="Dry") )*1.00
/
COUNT( FILTER("history2",ISNOTBLANK([Action]) )*1.00

[Ryan_Wagner]

Thanks, @Suvrutt_Gurjar . I tried that, too, but it's performing the same way. Still not working. I am using a "percent" column type, but I also tried this as a "number" column. 

Wondering if I should give up on this idea...

[Ryan_Wagner]

Is it possible that my filter is counting ALL the rows in the "history2" table that have been labeled "dry?" The problem seems to be with how it is looking up the reference, unless I am wrong...

[Suvrutt_Gurjar]

Thank you for the update. In that case , you may want to once more verify  your expression whether it is resulting in correct number of numerator and denominator values. You could check each separately in a VC.  I think 66% means possibly 2/3 instead of expected 75 % which 3/4 ( your first result) . Second result you are expecting 100% (3/3) but the result shows possibly 2/3 ( 66%) 

So maybe numerator or denominator somehow are not getting all the record values that they should get as per your expectation.

[Ryan_Wagner]

That is a great idea, thanks @Suvrutt_Gurjar . 

I removed the division part and just tried to get a count of all the "dry" for a given row. Its still giving me (what I assume) is the total count of ALL "dry" for the whole table...

[Ryan_Wagner]

I also tried it without "_THISROW"...

[Marc_Dillon]

Yes of course it is giving a count for the entire table, you haven't told it to do otherwise. Is it a parent-child ref situation? If so, instead of FILTER(table...) you can do SELECT( [Related table][key-column]...)

[Ryan_Wagner]

I thought the "this row" component would isolate the results...Yes, it is a parent-child ref. I haven't had a lot of success navigating the SELECT function, but I'm definitely open to trying again...I hope I at least explained what I was after well enough.

[Ryan_Wagner]

So here's what I cooked up to try SELECT:

[Ryan_Wagner]

But, same wild results. This row should read as "2," at least in theory, no?

The "AssetNumber" is my key, by the way.

[Marc_Dillon · Accepted solution]

You are wildly mistaken about the role of _thisrow. It's function is to resolve any ambiguity of context. Imagine you're in the record of table1 with a column [category], and you're selecting across table2 by matching to its own [category] column. Using FILTER( table2 , [category]=[category] ) is going to return all records in the table, because every record's [category] column equals it's own [category] column. But you're wanting to match it to the current table1 record's [category], so you use _thisrow to specify which context to pull that column from, with FILTER( table2 , [category]=[_THISROW].[category] ).

No, your new SELECT expression is still selecting across the entire table, it is doing the same thing as before.

I mentioned that you can select across your [Related...] column, which you didn't seem to catch. You'd need to do one of the following, which are equivalent, but the former is more efficient.

COUNT(SELECT( [Related History2s][Asset Number] , [Action]="Dry" ) )

COUNT(FILTER( History2 , AND( [Action]="Dry" , [ref-column] = [_THISROW] ) ) )

[Ryan_Wagner]

Thanks. Apologies for missing the point. I don't consider myself an expert.