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:
Are you getting an error or just an incorrect result? Are you getting any value or is it null for everything?
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):
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.... :(
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:
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.
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
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.
Aggr.qvw 132.2 K
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
Group By Id ;
LOAD * , If(Date=FirstDate ,1 , 0) as Flag
Drop Table OrderTable;
Maybe there is a problem if a customer orders twice the same date.