CODEWITHSUNDEEP
spotfire
mkyhnry
mkyhnry

Reputation: 1

Count of customers scheduling service dates in consecutive years

I am brand new to Spotfire (using 7.7) and am hitting a roadblock on a simple task so am hoping for some assistance.

I need a count of customers that are scheduling service dates in consecutive years. Since users will be using different date filters I created a MaxServiceDate calculated column to give me the max date depending on the filter and from there subtract a year to give me a PreviousYear calculated column.

Then my thought is then do a distinct count of all CustomerIDs which have a ServiceDate that match the MaxServiceDate and PreviousYear. The issue I'm running into is I don't know how to group by or fix the CustomerID in order to loop through the ServiceDates and check if they have dates matching my criteria.

Thanks for any help.

Sample data:

Spotfire sample data

Upvotes: 0

Views: 70

Answers (2)

coomie
coomie

Reputation: 411

I think a calculated column like the following would meet your needs: 

If((Max(Year([ServiceDate])) over (Intersect([CustomerID])) - Min(Year([ServiceDate])) over (Intersect([CustomerID])) + 1)=UniqueCount(Year([ServiceDate])) over (Intersect([CustomerID])),"good","bad")

For each customer, take their max service date year and subtract their min service date year and add +1. This number should match the number of distinct years associated to that customer's service dates.

This will tell you if a particular customer (at least while you had that customer) had service done in consecutive years or not.

Upvotes: 0

Tyger Guzman
Tyger Guzman

Reputation: 758

You can create a secondary table with a pivot transformation having the max(ServiceDate) and customer ID. The join that table back with your original so that you have the maxdate for each customer on your main table. Then do a calculated column based on the Max(ServiceDate) and Service date to determine if > 365 days.

Pivot Transformation

Upvotes: 0

Related Questions