count by distinct customer and summed value by date

[#1]

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

[#2]

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)

[#3]

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

[#4]

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

[#5]

Thanks Vidyut.

Your data is:

LOAD * INLINE [
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.

Your pivot gives me:

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.

[#6]

Hi,

Try following expression (Look into attachment):

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

Kind regards,

JTPro

[#7]

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.

[#8]

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

[#9]

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

Any QV experts out there with a solution?

Thanks

[#10]

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