Duplicates - Need an aggr function or set analysis - help??

[#1]

Hi All,

Lets say I have two tables. One for customers and one for orders. In the customers table there is no date, but in the orders table there is an order date. I have a chart where I count cumulative order ids by their order date. I would now like to add in logic to show my cumulative customers as well- but since a customer may have many orders, I am only interested in counting their customer id when they first order. I have the following statement written which would return the first order date for a customer id:

aggr(min(ORDER_DATE),Customer_ID)

Now I am having trouble with the count part. I only want to count the customer id when the order date = aggr(min(ORDER_DATE),Customer_ID) , I have tried something like the following but its not working out:

COUNT(IF(aggr(min(ORDER_DATE),Customer_ID)=ORDER_DATE,Customer_ID))

Any suggestions or advice on what I am doing wrong?? The chart only uses the order date dimension.

Thanks,

Julia

[#2]

The expression looks pretty good to me. One thing I would try is reversing the conditionals. I don't know if it matters at all, but I always do the field = to the expression. So:

COUNT(IF(ORDER_DATE=aggr(min(ORDER_DATE),Customer_ID),Customer_ID))

Are you getting an error or just an incorrect result? Are you getting any value or is it null for everything?

[#3]

Hi

To achieve what you want I usualy create a flag in the order table to identify the first purchase, then in the chart you can filter using the flag.

How to create the flag ?

Try something like this

LEFT JOIN (OrderTable)
LOAD Id, Min(Date) as FirstDate
RESIDENT OrderTable
Group By Id ;

OrderTable2:
LOAD * , If(Date=FirstDate ,1 , 0) as Flag
Resident OrderTable;
Drop Table OrderTable;

Maybe there is a problem if a customer orders twice the same date.

Regards

JJJ

[#4]

Hi all,

Thanks for your help - sorry for the delay in answering - long weekend here in Canada. 🙂

JJJ- This will not work as this needs to be dynamically calculated base on the users filter selections, so I need to do it in the back end of the chart.

NMiller- Getting an incorrect result would be the best way to put it. It seems to be working in some cases, but not in others. I tried putting it in a table just to see what was going on with the following statement (took out the count for testing purposes):

IF(ORDER_DATE=aggr(min(ORDER_DATE),Customer_ID),Customer_ID,'n/a'))

The dimensions I have in this table are Customer_ID and ORDER_DATE - so what I would expect to see is for a customer with multiple orders is a couple of rows with n/a and only one row with the customer id when the date is the minimum. I am getting this in some cases but in others I get all n/a's.....I can't seem to figure out why the IF statement works for some but not others.... 😞

Thanks,

J

[#5]

Try placing just the Aggr() function in an expression and see if it is returning results for every Customer_ID. I believe only one row per Customer ID would be populated.

You may need a TOTAL modifier in your Min() function. Try:

IF(ORDER_DATE=aggr(min(TOTAL ORDER_DATE),Customer_ID),Customer_ID,'n/a'))

It could be that the Aggr() is still calculating within the context of the dimensions. The TOTAL modifier should get past that. I'm not sure that is the problem though.

[#6]

Yes I think I am doing something wrong in the Aggr() function. I want my data to be something like follows:

Customer ID	OrderID	OrderDate	aggr(min(OrderDate),CustomerID)	IF(OrderDate=aggr(min(OrderDate),CustomerID), 'first order','n/a')
1	1	5/1/2010	5/1/2010	first order
1	2	5/2/2010	5/1/2010	n/a
1	3	5/3/2010	5/1/2010	n/a

But what I am actually getting is:

Customer ID	OrderID	OrderDate	aggr(min(OrderDate),CustomerID)	IF(OrderDate=aggr(min(OrderDate),CustomerID), 'first order','n/a')
1	1	5/1/2010	-	n/a
1	2	5/2/2010	5/1/2010	n/a
1	3	5/3/2010	-	n/a

I tried adding the TOTAL qualifier to the min expression but that doesn't seem to make a difference.

[#7]

When I put this in a sample app, I got the Aggr result as a number, not a date. On your chart, it looks like a date, but I'm bot sure if that is formatted on the chart that way. It also looks like our expressions had an extra closed parenthesis on the end. Removing that got the result you wanted (with the limited data set I tested). The TOTAL is not required.

I've attached the sample.

[#8]

Thanks for all of your help!! - I think actually what I needed was the NODISTINCT clause.