2 Replies Latest reply: Jan 9, 2012 1:30 AM by guyyu200109 RSS

    Number of Order in the First 3 Months

      I am very new to QV, am hoping to get some nested aggregate calculations with Count and Min functions but I am not too sure how I can get it done with the aggr function...


      I am hoping to get the number of order of each customer in the first 3 months since his first order date.


      To simply, the table only contains 3 fields:

      - Customer_Num

      - Order_Num

      - Order_Date


      I know I can get the first order date by

      MIN(Total <Customer_Num> Order_Date)


      And to get the date of the end of the period, I will need to use AddMonth

      AddMonths(MIN(Total <Customer_Num> Order_Date), 3)


      But then, if I want to count the number of Order_Num where Order_Date falls into the first 3 months, I will need to nest the above calculation with a "Count" (with if statement)

      Count(if(Order_Date <= AddMonths(MIN(Total <Customer_Num> Order_Date), 3), Order_Num))


      That is the logic of what I want to get, but I am not sure how I should use aggr function with the correct syntax...  Can anyone help? 


      Sorry that I cannot post the original qvw here since the what I am working on is more complicated, but in brief above is what I want to achieve.


      Any help is highly appreciated.  Thanks in advance.





        • Number of Order in the First 3 Months
          Celambarasan Adhimulam


               Use set analysis for this type of problems.

               Calculate the first order date in the script itself because it will be easy to use.

               COUNT( {<Order_Date={"<=$(=AddMonths(First_Order_Date, 3))"}>} Order_Num)

          and make sure that your Order_Date field is same as the format which returned by the Add months function.


          Hope this may helps.


            • Re: Number of Order in the First 3 Months

              Thanks Calambarasan,


              Yeah it would be great if I could do that.  However that was controlled by IT and end year like me cannot make such change (and if I submit a request to IT, it will easily take 3 months for this kind of amendment).  And hence I was wondering is there any easier way by learning the real QV function.  I know it can be done easily in tools like Cognos but I am pretty new to this QV and hence need some help.


              Once again, thanks for your suggestion.