P() and E() set analysis between dimension values

[#1]

Hello good folks of the net,

I have a calculation that I am struggling with. Is anyone able to offer some insight?

I have a fairly straightforward report requirement where I want to count the number of customers in a period. I also need to know how many are new and how many have been lost. I have tried using e() set analysis to exclude customers who exist in the previous period but I cant seem to find the correct syntax, if indeed that is the correct way to do it.

I have attached a sample document with the data boiled down to the bare minimum. As an example, Account 10006 and 10007 appeared in 201302 but not in 201401, so the new count for 201402 should be 2.

Can anyone suggest a solution?

Thanks

[simondachstr]

Can you be more specific: You want to count the number of customers in a period. What period? How do you determine the new and how do you determine the lost ones?

Furthermore, I don't see year month 2013 02 in your sample data set - am I missing something?

[#3]

Sorry, that was a typo. It should have been 201402, not 201302. I would like to count customers who are new in a period, i.e. they didnt appear in the previous period. In my example above, 5 customers traded in 201401. In 201402 another 2 traded (10006, 10007) so the total number of customers is 7 and the number of new is 2.

[simondachstr]

Thanks for the clarification. If let's say in 201403 there were 5 customers. 2 dropped out in 201404 but at the same time 2 new came in - even though the count of customers is the same, you expect the number of new customers to be 2, is that correct?

[#5]

Correct. The final table should look something like this:

	201401	201402	201403	201404
Active	5	7	7	8
New	5	2	1	2
Lost	0	0	1	1
		New:	New:	New:
		10006	10008	10004
		10007		10009
			Lost:	Lost
			10004	10002

I've included the detail of what makes up each new/lost calculation below for clarity.

[#6]

I'd be just as interested to hear if anyone just thinks this isnt possible, as well as to hear a solution. If the consensus is that its not possible then I can carry on and create it in the data model.

[jagan]

Hi Watson,

Try this script

Orders:

LOAD Order_No as Account,

     Period,

     If(Previous(Order_No) <> Order_No, 'New', 'Old') AS Flag

FROM

(ooxml, embedded labels, table is Sheet1)

ORDER BY Account, Period;

and expression for New as

=Count({<Flag={'New'}>}Distinct Account)

Hope this helps you.

Regards,

Jagan.