topic Re: Set Analysis Question in QlikView

Set Analysis Question

mjamesking — Fri, 05 Sep 2014 19:00:38 GMT

Hi,

Wondering if what I need to do is possible with set analysis.

I have two tables in QlikView the first contains customers, with a joined date

Customers:

CustomerID

JoinDate

The second, Order with OrderDate and the Customer ID of who placed it

Orders:

OrderID

CustomerID

OrderDate

I want to know, for a selected date period how many customers existed at the end of the period who had NOT placed an order. I have a variable which gets set to the date an the period end. The following formula gives me the number of members at the end of the period.

=COUNT({<JoinDate = {"<$(vPeriodEnd)"}>} DISTINCT CustomerID)

How can I add to this to include the criteria that I only want to count users who have no orders placed by the end of the period and still continue to use set analysis?

Thanks in advance.

Re: Set Analysis Question

maxgro — Fri, 05 Sep 2014 19:15:14 GMT

=COUNT({<JoinDate = {"<$(vPeriodEnd)"}, CustomerID = {“=Count(OrderID) = 0”}>} DISTINCT CustomerID)

Re: Set Analysis Question

mjamesking — Fri, 05 Sep 2014 19:44:39 GMT

Thanks Massimo, but I want to also include a criteria that the OrderDate needs to be before the end of the period. If i do the following, I get an error I expect this is because of the inclusion of the double quotes " within the formula that is enclosed in the quotes ".

=COUNT({<JoinDate = {"<$(vPeriodEnd)"}, CustomerID = {"=Count({<OrderDate = {"<$(vPeriodEnd)"}>} OrderID) = 0"}>} DISTINCT CustomerID)

Any ideas?

Re: Set Analysis Question

maxgro — Fri, 05 Sep 2014 19:49:57 GMT

try single (you can often change ' with " in set analysis)

{'<$(vPeriodEnd)'}

Re: Set Analysis Question

mjamesking — Fri, 05 Sep 2014 20:09:53 GMT

Thanks again. Gives me zero values though, no error. There should be results.

However... this works, for those that have orders

=COUNT({<JoinDate = {"<$(vPeriodEnd)"}, CustomerID = {"=Count({<OrderDate = {'<$(vPeriodEnd)'}>}OrderID) > 0"}>} DISTINCT CustomerID)

Strange?

Re: Set Analysis Question

mjamesking — Fri, 05 Sep 2014 20:13:47 GMT

this seems to give the correct answer, would you agree it's valid?

=COUNT({<JoinDate = {"<$(vPeriodEnd)"}> - <JoinDate = {"<$(vPeriodEnd)"}, CustomerID = {"=Count({<OrderDate = {'<$(vPeriodEnd)'}>}OrderID) > 0"}>} DISTINCT CustomerID)