7 Replies Latest reply: Oct 12, 2010 3:13 PM by juliaford RSS

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

      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

        • Duplicates - Need an aggr function or set analysis - help??
          Neil Miller

          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?

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

              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

                • Duplicates - Need an aggr function or set analysis - help??
                  Neil Miller

                  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.

              • Duplicates - Need an aggr function or set analysis - help??
                Jean-Jacques Jesua

                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