How to Count only distinct years?

[mrthomasshelby]

Hello,

I have a table as shown below:

No	Category	Description	Customer	Industry	Duration	Value	Location	Start Date	End Date	Contact
1	Railings	A	ABCD	Sports	24	500000	Australia	3/25/2010	3/25/2012	Z
2	Railings	B	EFGH	Oil	37	20000	Indonesia	6/12/2012	6/12/2015	Y
3	Railings	C	IJKL	Metals	37	250000	Cambodia	6/12/2012	6/12/2015	X
4	Railings	D	MNOP	Steel	37	450000	Brunei	6/12/2012	6/12/2015	D

Now I want to define a measure called 'Experience' that counts the number of distinct years that the vendor above has provided the services in. For the example above, we can see that the vendor has provided these 4 services in the years: 2010,2011,2012,2013,2014,2015, so the value of 'Experience' will be 6. I am also doing this in the script part for your information. I have tried taking the sum of Year difference between the dates but the problem with this is it sums up even the non-distinct years and gives an inflated value. Please help me out with the logic for this. Thanks in advance!

stalwar1‌ kaushik.solanki‌ tresesco‌

[isingh30]

Where is year 2011,2013 & 2014?

Thank you!

[wade12]

use year() function to strip  out the year from the date and just work with that.  ... just a suggestion.

[mrthomasshelby]

Hi! For service No.1, the start date is 3/25/2010 and End date is 3/25/2012. So the service was in years 2010,2011,2012. Similarly, for service No.2, start date is 6/12/2012 and End date is 6/12/2015. So the service was in the years 2012,2013,2014,2015. So overall the vendor has provided services in the years 2010,2011,2012,2013,2014,2015. Hope that makes it more clear.

[sunny_talwar]

Look at "Looping over the same record" section of the below blog

Loops in the Script

[kaushiknsolanki]

Try this,

(Year(EndDate) - Year(StartDate)) +1

as your new expression which will tell you how many numbers of year that vendor serviced you.

For example in your above case Row 1.

(2012-2010) + 1 will be 2+1 = 3

Regards,

Kaushik Solanki

[mrthomasshelby]

This is exactly what I tried but the problem with this is it doesn't give the overall experience of the vendor, just experience at each line item level. I want to calculate the overall experience of a vendor. For example, this vendor has provided services in the years 2010,2011,2012,2013,2014,2015 from all the listed service numbers. Thanks!

[mrthomasshelby]

Can you maybe give me an idea as to how I can use this in my case Sunny? Thanks!

[sunny_talwar]

Create Date, MonthYears, or Year from the date range and then you can then easily count the distinct years

[mrthomasshelby]

Here's the script I tried:

T1:

LOAD Vendor,

     No.,

     [Equipment Category],

     Description,

     [Name of Client],

     Industry,

     [Duration (No of months)],

     Value,

     [Location of Services],

     [Start Date],

     [End Date],

     [Client’s Contact Person / Person-in-Charge]

FROM

(ooxml, embedded labels, table is Sheet1);

Dates:

Load Year([Start Date]+ IterNo() - 1 ) as Year

Resident T1

While IterNo() <= Year([End Date]) - Year([Start Date]) + 1 ;

But I'm not getting the correct result. Can you point what I'm doing wrong?Also, I want this measure to be associated with the 'Vendor'.   I'm attaching the app too just in case. Thanks a lot!