9 Replies Latest reply on Jan 30, 2019 3:44 AM by TIm Garstin

# Book9.twbx  What % of Customers ordering items in 2011 also ordered items in 2012? (use the customer ID to identify the customer)

• ###### 1. Re: Book9.twbx  What % of Customers ordering items in 2011 also ordered items in 2012? (use the customer ID to identify the customer)

Hi Shiva,

Refer

Thanks

sankar

• ###### 2. Re: Book9.twbx  What % of Customers ordering items in 2011 also ordered items in 2012? (use the customer ID to identify the customer)

Hi Shiva,

Here is how you can create three calculated fields to get results:

Customers Bought in Base Year

IF

{FIXED [Customer Name]: SUM(IF YEAR([Order Date]) = 2011 THEN [Sales] END)}

> 0 THEN [Customer Name] END

Customers Bought Again

IF

{ FIXED [Customer Name]:

SUM(IF NOT(ISNULL([Repeat Customer])) AND YEAR([Order Date]) = 2012 THEN [Sales] END)}

> 0

THEN [Customer Name] END

Repeat Purchase Percent

COUNTD([Customers Bought Again])

/

COUNTD([Customers Bought in Base Year])

Hope that helps.

~ Ashish Singh

• ###### 3. Re: Book9.twbx  What % of Customers ordering items in 2011 also ordered items in 2012? (use the customer ID to identify the customer)

hi ashish singh thanks for your support.but in the second calculated field what do i have to take for [Repeat customer]?

• ###### 4. Re: Book9.twbx  What % of Customers ordering items in 2011 also ordered items in 2012? (use the customer ID to identify the customer)

Hi Shiva,

You may want to create Sets

from both the first and the second

LOD calcs written above by Ashish.

Then create a new Set as an Intersection of both.

This would be your [Repeated Customers].

Everything else is just plain simple, I guess :-)

Yours,

Yuri

• ###### 5. Re: Book9.twbx  What % of Customers ordering items in 2011 also ordered items in 2012? (use the customer ID to identify the customer)

Hi Yuriy, Yuriy Fal,

Following your approach I created two sets using these logics:

2011Customers?

if { FIXED [Customer ID],YEAR([Order Date]):MAX(IF YEAR([Order Date])=2011 then 1 else 0 END)} = 1 then [Customer ID] end

2012Customers?

if { FIXED [Customer ID],YEAR([Order Date]):MAX(IF YEAR([Order Date])=2012 then 1 else 0 END)} = 1 then [Customer ID] end

Both seems to be working fine individually. Even two separate sets created out of them give me 2011 and 2012 customers separately and respectively. But I am not getting any option of combining them. A right click does not contain 'Create Combined Set'. Can you please suggest why?

Thanks,
Vivek

• ###### 6. Re: Book9.twbx  What % of Customers ordering items in 2011 also ordered items in 2012? (use the customer ID to identify the customer)

Hi Vivek,

For the Sets to be combined

they should be made from

the same Dimension(s).

So one could create both years sets

as Conditional Aggregate Filters --

using the same [Customer ID] Dimension.

Please find the attached as an example.

Yours,

Yuri

1 of 1 people found this helpful
• ###### 7. Re: Book9.twbx  What % of Customers ordering items in 2011 also ordered items in 2012? (use the customer ID to identify the customer)

Thanks a lot Yuriy for your time on this. All good now. Cheers.

• ###### 8. Re: Book9.twbx  What % of Customers ordering items in 2011 also ordered items in 2012? (use the customer ID to identify the customer)

Vivek, you're welcome.

• ###### 9. Re: Book9.twbx  What % of Customers ordering items in 2011 also ordered items in 2012? (use the customer ID to identify the customer)

Hi Shiva, the solution here is through creating 3 calculated fields. The first two calculations get the customer ID if the conditions are met (1. did the customer buy in 2011? 2.was the customer retained in 2012, who ordered in 2011?). The third calculation counts the number of customers who bought again in 2012 who also bought in 2011 / the number of customers who bought in 2011.

1. Get the customers (IDs) that ordered in 2011(your base year)

e.g. [Customers who bought in 2011 (base)]

IF

{ FIXED [Customer ID] : SUM(IF YEAR([Order Date]) = 2011 THEN 1 ELSE 0 END)}

// get customer ID if true

> 0 THEN [Customer ID] END

2. Get the customer (IDs) that bought again in 2012 who also originally ordered in 2011

e.g. [Customers who bought again in 2012 who also ordered in 2011]

IF

{ FIXED [Customer ID]: SUM( IF NOT ISNULL([Customers who bought in 2011 (base)])

AND YEAR([Order Date])= 2012 THEN [Customer ID] END)}

//get customer ID if true

>0 THEN [Customer ID] END

3. Get the % of customers that were retained in 2012 who ordered in 2011

e.g. [Repeat Customers (%)]

COUNTD([Customers who bought again in 2012 who also ordered in 2011])

/

COUNTD([Customers who bought in 2011 (base)])

I have attached a workbook example for your reference. Please mark this thread as resolved.

Thanks,

Tim

1 of 1 people found this helpful