Data Intersection Question

[ecabanas]

Hi

I'm creating an analysis as follows:

CustomerID	Salesid	Date
C000001	A	31/01/2015
C000002	B	31/01/2015
C000003	C	31/01/2015
C000004	D	31/01/2015
C000001	E	28/02/2015
C000002	F	28/02/2015
C000005	G	28/02/2015
C000006	H	28/02/2015
C000005	I	31/03/2015
C000006	J	31/03/2015
C000007	K	31/03/2015
C000008	L	31/03/2015

When I select the month has to return to me the number of customers that bought the current month (selected) AND the previous month

Many thank's guys

Eduard

[swuehl]

There are some solutions for counting new / lost / returning customers here in the forum, for example:

New/Lost/Returning/Loyal Customers

[ecabanas]

Hi Swuehl,

First of all many thank's for your help , I read the link and I'm trying to do with this formula, but did not works, could you help to me?

count({<CalendarYear={'$(VYear)'},CalendarMonthName={'$(VMonth)'},CUSTACCOUNT

=P({<CalendarYear={'$(VYear)'},CalendarMonthName={'$(VPriorMonth)'}>}CUSTACCOUNT)>} distinct CUSTACCOUNT)

Many thank's for your patience

Eduard

[swuehl]

I would start with checking

a) that each variable expands to a value and the value match field formats and possible values in fields

b) you are not selecting Jan as VMonth (I think your logic has a flaw at year Change)

c) that you are not selecting on other calendar fields than CalendarYear and CalendarMonthName, since this might render the set expression to an incompatible set (using the modified field values and selected field values).

All in all, it's hard to tell without knowing more details. Any chance you upload a small sample QVW?

[ecabanas]

Hi Swuehl

I created a doc and if you can help to me to create the formula. there is a table named did not work

Many thank's and sorry for the headache

Eduard

[swuehl · Accepted solution]

1) Define your VMonth and VPriorMonth variables with a leading equal sign:

=CalendarMonthNumber-1

2) Since you selected in CalendarMonthName, you need to clear this field in the p() function to avoid an incompatible set:

count({<CalendarYear={'$(VYear)'},CalendarMonthNumber={'$(VMonth)'},CustomerID=P({<CalendarYear={'$(VYear)'},CalendarMonthNumber={'$(VPriorMonth)'}, CalendarMonthName= >}CustomerID)>} distinct CustomerID)

You can also remove the first to set modifier, since they won't change anything compared to your default set:

count({<CustomerID=P({<CalendarYear={'$(VYear)'},CalendarMonthNumber={'$(VPriorMonth)'}, CalendarMonthName= >}CustomerID)>} distinct CustomerID)

[ecabanas]

Hi Swuehl

Many mnay thank's the problem that i was working was to exclude the CalendarMonthName inside the function...but now I have new problem, when I want to compare january 2015 Vs 2014 this formula did not work, did you know how to solve?

Many many thank's again

[swuehl]

You can create a MonthIndex field in your script like

LOAD

     Year,

     MonthNum,

     Year*12+MonthNum AS MonthIndex,

     ...

MonthIndex should now show consecutive numbers across all years, now operate on MonthIndex to find the previous month in your set analysis.

[ecabanas]

Many many thank's

If you come to Barcelona a Paella is waiting you 😉