I want to include a total record count in the titles of a number of views. I plan to add a VC in each table needed (and add that to each slice) Whatโs the least costly formula to count records (filter, select, count)? I donโt want to hurt sync speed if I can avoid it. Thoughts appreciated.
Solved! Go to Solution.
The exact error might be useful.
Spaces are fine, but I donโt know about parentheses. I suspect the parens are confusing the expression parser, which likely sees activities(open)
, a function call, followed by [_rownumber]
, a column reference.
I donโt know for certain but, based on my limited experience, I would think that filter and select would tend to be more costly than count. Iโm interested in the answer to this because Iโm using โcountโ in some virtual columns in my app to do this. Iโm using
count(Slice name[Key])
In this case the slice has done what filter or select might do. Of course, if you donโt have a slice and need to pick out a subset of records to count, I think used need to use filter or select.
Thanks @Kirk_Masden. On my case I used the following in the VC
COUNT(FILTER(Activities, IN([State], {"(1) OPEN", "(2) IN-PROGRESS","(3) PAUSED"})))
But then had to take the MAX of this column in the view to get a single number. I have to take another look because there is likely a simpler wayโฆ
*My actual use case is, because the histogram chart cannot show the total record count it is based on (just shows the hover popup per bar), I want to modify the view title to shown count of the filtered record set. My approach was to add a VC, but am concerned that this has to be recalculated for every single record, each sync, rather than just when the chart view is accessed - so speed is a concern.
Other approaches appreciated!
@Mike why do you need to add the MAX for the number? The result from the COUNT expression is a number already?
Hi @Aleksi. I did this really quick last night, but without MAX, i was getting a count returned for each and every record - basically a list of counts, but all the same total count.
Looked like: 117, 117, 117, โฆ
So, just did MAX so I got one number for count of all records. Before solving what I did wrong there, figured I would ask about the โleast expensiveโ approach to getting a record count returned from a slice (which is actually all I want)โฆ
You didnโt make any mistakeโฆ when you are calculating something with the virtual column, it calculates the formula for all records. IF the formula is very generic like COUNT(Table[Column]), the result is the same for all recordsโฆ but still itโs just one number.
Just perform the count in the viewโs Display Name expression. I would most definitely not do it in a virtual column attached to each row of the table. Yikes!
As for the best formula, the fastest is likely to be a raw column reference (e.g., MyTable[_ROWNUMBER]
). SELECT() and FILTER() are likely to be slower.
LOLโฆ thanks @Steve! I thought a VC might be trouble.
When you say โMyTable[_ROWNUMBER]โ, can โMyTableโ be a slice? I need that as the slice has a Row filter condition. I can get Activities[_ROWNUMBER]
to work, but thatโs the entire table.
What I really want to do is to use a slice named โActivities (Open)โ as the source for COUNT like:
COUNT(Activities (Open)[_ROWNUMBER])
but that does not work (and quotes around slice name did not help).
Soโฆ is there any way to use the slice name in the view Display Name expression? Or do I need to rebuild the entire slice condition into the view Display Name expression?
Current condition is: AND([State]<>"(4) Closed", [State]<>"(5) Canceled",[ID#]<>"undefined")
UPDATE: I deleted to VC and used the following in my view Display Name expression:
โOpen Activities โ&โ(โ&
COUNT(FILTER(Activities, IN([State], {"(1) OPEN", โ(2) IN-PROGRESSโ,"(3) PAUSED"})))
&")"
If anyone knows a better approach, feel free to chime in!
ps: disadvantage of this approach is, I need to maintain this formula both in the slice and the view. Not the most efficientโฆ
Yep!
Hmmmโฆ couldnโt get that to work with slice name Activities (open)
. Forget the exact error but will re-check.
Tried:
Activities (open)[_ROWNUMBER]
โActivities (open)โ[_ROWNUMBER]
โActivities (open)โ[_ROWNUMBER]
Will have to try again. If the table name has spaces, does it need to be surrounded in quotes?
The exact error might be useful.
Spaces are fine, but I donโt know about parentheses. I suspect the parens are confusing the expression parser, which likely sees activities(open)
, a function call, followed by [_rownumber]
, a column reference.
Using Activities (open)[_ROWNUMBER]
as the chart view Display Name
results in the error of:
โUnable to find function โACTIVITIESโ, did you mean โINITIALSโ?โ
Will rename my slice and report backโฆ
Bingo!
CONCLUSION: never us parenthesis in Slice names!
COUNT(Activities (open)[_ROWNUMBER]) generates an โUnable to find functionโ error.
COUNT(Activities_open[_ROWNUMBER]) works perfectly.
Heh.
Iโve reported this confusing behavior.
In what way didnโt it work?
User | Count |
---|---|
40 | |
36 | |
33 | |
23 | |
17 |