10 Replies Latest reply: Nov 23, 2009 3:43 PM by DANIEL CHOTZEN

# count by distinct customer and summed value by date

My brain isnt working today

I have a transaction table with customer id transaction date and transaction value. Customers may make multiple transaction per day.

I have a pivot table with transaction date as dimension and sum(value) and count(distinct customer id) as expressions

That all works fine, but what I need is another column with count of distinct customer id of customers spending more than £50 per day. Everything I try bring back junk.

e.g.

Date Total Order Value Total Customers Total Customers > £50

[TXDate] sum(TXValue) count(distinct Customerid) ??????

1/1/2009 £1223 123 12

I'm sure its easy, just can't sus it

Thanks

• ###### count by distinct customer and summed value by date

Hi,

You must use the Set Analysisin your expression.

You can have a llok in the Reference manual, book 3, page 333 for an exemple.

I'm not fluent with Set Analysis :) but i think your expression should look something like:

count(\$<customer = {=value > 50}> distinct customer id)

• ###### count by distinct customer and summed value by date

Thanks

but I want sumation of transation value for each customer across each day > 50.

Assume a mixture of set and aggr, but I cant get anything to work properly

• ###### count by distinct customer and summed value by date

Hi,

The followinf expression will help:

Count(distinct aggr(If(aggr(Sum(Value),CustomerId)>50,CustomerId),CustomerId))

I have attached the example QVW File

Regards,

Vidyut

• ###### count by distinct customer and summed value by date

Thanks Vidyut.

txDate, CustomerId, Value
01/01/2009, 1, 200
01/01/2009, 2, 20
01/01/2009, 3, 60
02/01/2009, 1, 20
02/01/2009, 2, 35
02/01/2009, 4, 25
02/01/2009, 5, 25
03/01/2009, 6, 20
03/01/2009, 6, 35
];

i.e. there are 2 customers who's total value is over 50 on 1/1/2009.

Date Dist Ciut Count(distinct aggr(If(aggr.......

1/1/2009 3 3

2/1/2009 4 0

3/1/2009 1 1

Clearly 3 is not right for 1/1/2009

I tried changing your expression to:

Count(distinct aggr(If(aggr(Sum(Value),txDate,CustomerId)>50,CustomerId),CustomerId))

but that gives me 1 for 1/1/2009, still not right.

..and get this, if I add txDate as a listbox and select just 1/1/2009, the pivot then gives 2

Whats going on here? I'm confused.

• ###### count by distinct customer and summed value by date

Hi,

Try following expression (Look into attachment):

Count({<Value = {">50"}>} distinct CustomerId)

Kind regards,

JTPro

• ###### count by distinct customer and summed value by date

Thanks JTPro

Good try, but you have the wrong count for 3/1/2009. Customer 6 has total £55 value for that day.

I still dont get why the aggr expression changes when the day is selected in a listbox.

• ###### count by distinct customer and summed value by date

Hi,

My next try would be something like this:

=COUNT( {\$<CustomerId = {"=Sum(Value)>50"}>} CustomerId)

, but I don't know why the Sum inside the set analysis condition gives amount from the whole set of data. It should take into consideration the dimension in the pivot table.

Maybe someone more experienced can help?

Kind regards,

JTPro

• ###### count by distinct customer and summed value by date

This wont aggregate the total value by customer and by day.

Any QV experts out there with a solution?

Thanks

• ###### count by distinct customer and summed value by date

Sorry, was busy last couple of days. I modified the expression in my application to be following:

Count(distinct If(aggr(Sum(Value),CustomerId,txDate)>50,CustomerId))

and it works fine now. Could you please check and confirm?

thanks,

Vidyut

• ###### count by distinct customer and summed value by date

this one works like charm

great idea thanks

mansyno