topic Eliminating lines for a total in QlikView

Eliminating lines for a total

Tue, 02 Nov 2010 06:43:51 GMT

Hi QV Community,

I have an expression in a straight table where all I want is the total (min, max and avg) but I'm getting a total per auction.

My expression is:

=if(Auction_ID > '13520072', count (distinct (Buyer_Number)))

I only have one dimension which is Auction_ID.

The result I'm getting is

The result I want is:

Please could someone assist me.

thanks,

Shane

sparur — Tue, 02 Nov 2010 07:22:28 GMT

Hello.

If I understand you, you should use a Set Analysis:

count( {<Auction_ID = {">13520072">} distinct Buyer_Number)

Tue, 02 Nov 2010 07:44:02 GMT

hi, tried that, it still gives me a line by line breakdown of Buyer_Number per Auction.

Tue, 02 Nov 2010 08:52:26 GMT

All that has ever worked for me in this respect is to leave the dimension blank. Remove Auction_id and it should leave you with the total.

Tue, 02 Nov 2010 09:09:02 GMT

Thanks for the reply DK_ASN.

When I remove the dimension, the outcome changes and the total is then incorrect.

Tue, 02 Nov 2010 09:15:27 GMT

Try an aggr. Something in the line of (un-tested):

count(distinct(aggr(if(Auction_ID > '13520072',Buyer_Number),Buyer_Number,Auction_ID)))

Tue, 02 Nov 2010 09:39:40 GMT

still gave me the same outcome but I came up with a different solution. I used the loading script to get what I want:

"Buy_Count":

LOAD

count ("Buyer_ID") AS "Buyer_Count"

RESIDENT Deposits

WHERE "Auction_ID" > '13520072'

GROUP BY "Auction_ID";

Thanks for the responses