Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

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.



Tags (3)
2 Replies

Number of Order in the First 3 Months


     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.


Not applicable

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.