Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Regards,
Guy
Hi,
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.
Celambarasan
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.
Regards,
Guy