3 Replies Latest reply on Apr 12, 2019 12:42 PM by Ramzi El-Sayed

# LOD caluclations

I have 1000 customers   and need to find how many customers who purchased in 2015 were purchased in other years.

Ex: Number of customers purchased in year 2015 is 800 and how many in those 800 (who purchased in 2015) are purchased in 2016, 2017 and so on.

thanks,

Regards

• ###### 1. Re: LOD caluclations

the solution to your problem will depend on the structure of your data and how complete the data set is (i.e. are the data sparse ) please attach a twbx workbook with the data (at least some representative)

Thanks

Jim

• ###### 2. Re: LOD caluclations

Hi jim,

thanks for the reply.

Lets consider Sample super store data attached.

I am able to find how many customers are purchased in year. As you see below there are 637 customer purchased in year 2013 and 693 were purchased in year 2014.

Now my requirement is to find how many are purchased in 2013 (selected year) were also purchased in other years and their count. i.e. out of 637 in year 2013, how many were purchased on year 2012, 2011 and 2014?

similary if i select year 2014 (see pic 2),out of 693 in year 2014, how many were purchased on year 2012, 2011 and 2013?

pic 2:

• ###### 3. Re: LOD caluclations

I had a hard time with this, the solution:

create a parameter (Parameter 1) with the data type as an integer and add value 2011-2014 and input the same to display.

create a calculation (Calc 1) as follows:

{ FIXED [Customer ID] : SUM(IF Year(Order Date)=[Parameter 1] THEN 1 END)}

For the color (Color): create a boolean calc as DATEPART('year',[Order Date]) = [Parameter 1])

Place the Calc in Row and set to CNT

Order Date to Columns as Year

Color to color mark

Show parameter

and voila.

Thanks,

Ramzi

1 of 1 people found this helpful