Open sales opportunities per month

[#1]

Dear all,

I was already struggeling with finding an appropriate titel for this question but it should get clearer now.

I am pulling sales pipeline data from Microsoft Dynamics into QlikView and would like to create a bar chart for open opportunities at any point in time to see the development. The problem is, that I need to determine if an opportunity was open at any point in time by looking at the creation date and close date of each opportunity. To ease my task a bit, I already created creationdate_month, and actualclosedate_month so I don't need to worry about specific dates.

As dimension I use MonthID (201401, 201402,...) and the expression needs to evaluate if creationdate_month was existing before or created in the month AND closedate after the current month. A second expression should show those records where creationdate_month = actualclosedate_month. Result should be a bar chart with open opportunities per month.

Hope this is clear as mud 🙂

Thanks for your help in advance,

Henning

[Gysbert_Wassenaar · Accepted solution]

Perhaps you can use the approach described in this blog post: Creating Reference Dates for Intervals.

[#3]

Thanks a lot. If you can't formulate the right question, Google cannot help 🙂