topic How to count items in a chart in QlikView

How to count items in a chart

Wed, 09 Dec 2009 07:38:31 GMT

I have a very simple function that I can't figure out in Qliview. Easy to do in cognos but been spinning my wheels in qlikview.

I have a dimension field (purchase orders) that I want to count. Basically if I have the following purhase orders:

PO 1234

PO 1212

PO 1329

PO 2456

I ant to add a calculazted dimension so that I can get a result of 4. In cognos i'd do something like

count (Purchase_Order). In qlikview i get the msg // Error in calculated dimension when I used the following expression: =TextCount (distinct PO)

How to count items in a chart

johnw — Wed, 09 Dec 2009 08:29:18 GMT

QlikView won't let you do aggregations like count() in a calculated dimension without using aggr(). But aggr() requires you to aggregate BY something. You could fake it out by adding, say, a "total" field with no value disconnected from your data model, and do aggr(count(distinct PO),total)). But that really just begs the question of why you're making this a dimension in the first place if you only want a single row of data.

So what it sounds like you REALLY want is a table with NO dimension, and count(distinct PO) as an EXPRESSION.

How to count items in a chart

Thu, 10 Dec 2009 00:29:18 GMT

I think I was approaching it the wrong way. What I want to do is to create a count of my purchase orders in my EXPRESSIONS.

So I have a Pivot Table with two dimensions (SITE and USER). I guess you can call these the grouped fields.

How do I count the total number of purchase orders as an Expression? I'm sure it's simple to do, but can figure it out in Qlikview.

Site Users PO_count QTY

How to count items in a chart

Thu, 10 Dec 2009 00:54:21 GMT

Here is a screen shot of the problem I'm having. This hopefully should be more clear:

the total 188 is not correct and I'm not sure what this figure is.

Below are 2 more screen shots. dwilson should have 36 unique Purchase Orders and sidzan should have 2 unique purchase orders. I want the above chart to display all 6 Users with the correct total unique Purchase Orders on each line. right not it's showing 188 for all users which is not correct.

How to count items in a chart

johnw — Thu, 10 Dec 2009 01:02:51 GMT


John Witherspoon wrote: So what it sounds like you REALLY want is a table with NO dimension, and count(distinct PO) as an EXPRESSION.

How to count items in a chart

Thu, 10 Dec 2009 01:28:26 GMT

Uh... thanks. Can't believe I didn't try that. but I guess I just had a brain fart. Your help is much appreciated.