Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I want to create a straight table with dimension of "$Field" and expression of count() of field named in $Field. Pseudo code for expression would be something like:
=count( ValueOf($Field) )
I've tried:
=count($(='[' & [$Field] & ']'))
but that only works when one $Field value is selected.
Any ideas?
Thanks,
Rob
It's even better without the TOTAL. The the expression includes only the right field:
$(=concat('if($Field=' & chr(39) & $Field & chr(39) & ',count({1}[' & $Field & '])', ',') & concat(right($Field&')',1)))
Also, it's maybe better to use set analysis set identifier {1} to disregard any selection.
Hi Rob,
I have it also tried with this issue and it seems as if $Field as system-field couldn't used as normal fields. I assume as adhoc-variable is it not possible because adhoc returns always a single value and it will needed a individual value for each row. But with a variable as expression created in an expression-loop it worked. Alternatively could be used a script-loop for this or reading the own meta-data from the qvw. See the attachment for variants 1 and 2.
Each suggestions or improvements are welcome.
- Marcus
Hi Rob,
I think for count you can just use:
=sum($Rows)
..and for distinct count:
=FieldValueCount($Field)
- Ralf
Hi Ralf,
sum($Rows) returned for each field a table the same value and count ignored the null-values. Question is, what is deliberately?
- Marcus
I think this is the solution
Thanks to everyone for answers. (It took me a while with the new forum to realize I had replies!).
Ralf,
FieldValueCount($Field) works great for the distinct count! Sum($Rows) does not give me the correct count. It includes nulls and doubles up for multiple tables. I'm after the same result I would get from count.
Marcus & Micheal, thanks for the variable solution. That's a great idea. Not perfect for my purpose. I'm try to improve my "System Sheet" to see how much insight into the data I can produce -- cardinality, subset ratios, etc. All without requiring script or variables if possible.
-Rob
Hi Rob,
I probably found a solution following Marcus' nested if() approach by building the whole nested if-expression. I found out that the concat() aggregation function seems to be the only one which works with $Field:
$(=concat(TOTAL 'if($Field=' & chr(39) & $Field & chr(39) & ',count([' & $Field & '])', ',') & concat(TOTAL right($Field&')',1)))
Btw. no leading '='..
- Ralf
Hi Ralf,
great - it worked (with better performance as my suggestions).
- Marcus
Yea, it's workable but still not perfect. Who knows, it could easily exceed the max length of an expression (which I don't know).. For my opinion Count($Field) should just work.
It's even better without the TOTAL. The the expression includes only the right field:
$(=concat('if($Field=' & chr(39) & $Field & chr(39) & ',count({1}[' & $Field & '])', ',') & concat(right($Field&')',1)))
Also, it's maybe better to use set analysis set identifier {1} to disregard any selection.