Chart with count of $Field values

[rwunderlich]

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

[marcus_sommer]

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

[rbecher]

Hi Rob,

I think for count you can just use:

=sum($Rows)

..and for distinct count:

=FieldValueCount($Field)

- Ralf

[marcus_sommer]

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

[michael_maeuser]

I think this is the solution

[rwunderlich]

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

[rbecher]

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

[marcus_sommer]

Hi Ralf,

great - it worked (with better performance as my suggestions).

- Marcus

[rbecher]

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.

[rbecher · Accepted solution]

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.