topic Re: How to Count only distinct years? in QlikView

How to Count only distinct years?

mrthomasshelby — Fri, 25 May 2018 10:28:34 GMT

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‌

Re: How to Count only distinct years?

isingh30 — Fri, 25 May 2018 10:40:33 GMT

Where is year 2011,2013 & 2014?

Thank you!

Re: How to Count only distinct years?

wade12 — Fri, 25 May 2018 10:50:42 GMT

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

Re: How to Count only distinct years?

mrthomasshelby — Fri, 25 May 2018 10:54:57 GMT

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.

Re: How to Count only distinct years?

sunny_talwar — Fri, 25 May 2018 11:01:31 GMT

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

Loops in the Script

Re: How to Count only distinct years?

kaushiknsolanki — Fri, 25 May 2018 11:02:09 GMT

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

Re: How to Count only distinct years?

mrthomasshelby — Fri, 25 May 2018 11:14:30 GMT

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!

Re: How to Count only distinct years?

mrthomasshelby — Fri, 25 May 2018 11:57:19 GMT

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

Re: How to Count only distinct years?

sunny_talwar — Fri, 25 May 2018 12:03:21 GMT

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

Re: How to Count only distinct years?

mrthomasshelby — Fri, 25 May 2018 12:19:50 GMT

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!

Re: How to Count only distinct years?

sunny_talwar — Fri, 25 May 2018 12:25:54 GMT

Check this

T1:

LOAD 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],

Year([Start Date]) as StartYear,

Year([End Date]) as EndYear

FROM

[Book1.xlsx]

(ooxml, embedded labels, table is Sheet1);

Dates:

LOAD DISTINCT StartYear,

EndYear,

StartYear + IterNo() - 1 as Year

Resident T1

While StartYear + IterNo() - 1 <= EndYear;

Re: How to Count only distinct years?

wade12 — Fri, 25 May 2018 12:26:58 GMT

T1:

LOAD Vendor,

No.,

[Equipment Category],

Description,

[Name of Client],

Industry,

[Duration (No of months)],

Value,

[Location of Services],

[Start Date],

Year([Start Date]) as Year,

[End Date],

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

FROM

(ooxml, embedded labels, table is Sheet1);

Re: How to Count only distinct years?

sunny_talwar — Fri, 25 May 2018 12:29:19 GMT

But if you are doing this by Vendor, I would do it like this

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],

Year([Start Date]) as StartYear,

Year([End Date]) as EndYear

FROM

[Book1.xlsx]

(ooxml, embedded labels, table is Sheet1);

Dates:

LOAD DISTINCT Vendor,

StartYear + IterNo() - 1 as Year

Resident T1

While StartYear + IterNo() - 1 <= EndYear;

Re: How to Count only distinct years?

mrthomasshelby — Fri, 25 May 2018 13:02:16 GMT

Works like a charm! Understood it now! Thanks again Sunny!