Determine the number of transactions based the value in a dimension

[atanthony]

My data source is a Excel spreadsheet with these columns: CustomerID, Date, Amount and Group.  The group column contains a string that indicates if the amount is less than 1000 (<1000), between 1000 and 5000 (1000 to 5000), or over 5000 (>5000).

I have created a table graph with these columns:  Group  -  # of Transaction  -  Sum of Amount.  The group and Sum of Amount columns work as expected.  For the # of Transaction I used the Count(Group), but I get an Invalid Dimension error.

How can I get the number of transaction in each group?

[sergio0592]

You should use aggr() with

=aggr(Count(Transaction_Primary_Key), Group)

[Oleg_Troyansky]

Tony,

let's be specific with the terminology, because it makes a difference... So, you created a Straight Table chart, and you created a dimension (Group) and two expressions (Amount and # of Transactions) - correct?

in the # of transactions, you can count any non-key field from your fact table. You do not need to use aggr() in this case, unless you really need your column to be a Dimension and not an Expression - I can't see why you'd need that.

Learn advanced QlikView and Qlik Sense techniques in my book QlikView your Business.

cheers,

Oleg Troyansky

[atanthony]

Oleg,

The dimension on the table are Group and CustomerID.  The measure is Amount. # of Transaction is an expression. I've tried the following:  Count(Group) and Count(CustomerID) .  Both return and Invalid dimension error.

[MarcoWedel]

Hi,

check, if you erroneously defined this expression as calculated dimension.

hope this helps

regards

Marco