topic Re: Chart with count of $Field values in QlikView

Chart with count of $Field values

rwunderlich — Tue, 20 Aug 2013 04:47:50 GMT

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

Re: Chart with count of $Field values

marcus_sommer — Tue, 20 Aug 2013 14:58:56 GMT

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

Re: Chart with count of $Field values

rbecher — Wed, 21 Aug 2013 10:35:15 GMT

Hi Rob,

I think for count you can just use:

=sum($Rows)

..and for distinct count:

=FieldValueCount($Field)

- Ralf

Re: Chart with count of $Field values

marcus_sommer — Wed, 21 Aug 2013 11:34:13 GMT

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

Re: Chart with count of $Field values

michael_maeuser — Thu, 22 Aug 2013 10:43:02 GMT

I think this is the solution

Re: Chart with count of $Field values

rwunderlich — Fri, 30 Aug 2013 17:02:40 GMT

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

Re: Re: Chart with count of $Field values

rbecher — Mon, 02 Sep 2013 08:47:49 GMT

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

Re: Chart with count of $Field values

marcus_sommer — Mon, 02 Sep 2013 11:20:52 GMT

Hi Ralf,

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

- Marcus

Re: Chart with count of $Field values

rbecher — Mon, 02 Sep 2013 11:33:44 GMT

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.

Re: Re: Chart with count of $Field values

rbecher — Tue, 03 Sep 2013 09:16:25 GMT

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.

Re: Chart with count of $Field values

rwunderlich — Tue, 03 Sep 2013 15:30:09 GMT

Ralf & Marcus,

Good solution. I'd like to mark them both correct but only one allowed.

-Rob

Re: Re: Chart with count of $Field values

rbecher — Tue, 03 Sep 2013 21:01:30 GMT

Just to attach an example as a show case (integrated in a funny ancient QV app ;-).

Also added $Table as dimension but this seems to be a bit confusing. Key fields always have a count result of all occurances in all tables. But one can play around with dimensions and other expressions to find things.

Re: Re: Chart with count of $Field values

marcus_sommer — Wed, 04 Sep 2013 07:55:17 GMT

Ralf, excellent solution!

Re: Chart with count of $Field values

Mon, 05 May 2014 05:44:49 GMT

Hello There,

It has been a long time since this thread is active. But it is usefull to for my reffrence.

My question:

How could we use something like the experssion that was dicussed to count the Blank values within a field Blank =''

$(=concat('if($Field=' & chr(39) & $Field & chr(39) & ',count({1}[' & $Field & '])', ',') & concat(right($Field&')',1)))

Re: Re: Chart with count of $Field values

rbecher — Mon, 05 May 2014 09:13:54 GMT

Hi Rayana,

this work to count fields with an empty string:

$(=concat('if($Field=' & chr(39) & $Field & chr(39) & ',count({1<[' & $Field & ']={""}>}[' & $Field & '])', ',') & concat(right($Field&')',1)))

- Ralf

Re: Re: Chart with count of $Field values

Mon, 05 May 2014 10:22:58 GMT

Thanks alot Ralf. Currently I am using this expression is QVDobserver.QVW you have created and it is perfomance is very solw as am loading a QVD that contain ~40,000 records but I am getting what I am expecting. Thanks any way and I looking forward to dicuss any perfomance tweaking ideas

Re: Re: Chart with count of $Field values

rbecher — Mon, 05 May 2014 10:35:11 GMT

I was facing also perfomance issues. The only work around so far was to disable expression you do not need at the moment, so only a few are enabled at one time..

Re: Chart with count of $Field values

kristof_j — Thu, 07 Aug 2014 11:22:24 GMT

And is it possible to add the fieldvalues to it?

What I want is a table with all or a selection of fields and with some expressions I could see de difference between 2 alternate states.

Something like this exemple:

Field Value state1 state2 difference

Country A 100 80 20

Country B 100 120 -20

Product X 6 6 0

Product Y 10 0 10

Re: Chart with count of $Field values

Sat, 07 Mar 2015 09:55:39 GMT

hi Ralf , that's excellent but I have a problem

when total number of field from all tables exceed the 99 all expressions return NULL

and when you select a table from any filter that's comeback with value !!

for test you can add some table from any source an see when field count exceed 99 what happen

Hassan

Re: Chart with count of $Field values

rbecher — Mon, 09 Mar 2015 15:09:37 GMT

Hi Hassan,

can you upload a sample QVW?

- Ralf