5 Replies Latest reply on Oct 18, 2017 10:53 AM by craig.mewis

    Date as Axis and in Calculated Field

    craig.mewis

      Hi,

      I have service technician data. Ideally, when I send a service tech to a customer's house they fix the problem and that's the last of it. Unfortunately sometimes we need to send a repeat visit to fix remaining problems. If you have one tech visit to your home, I don't care about you. If you have two visits in less than 6 months, I want to keep an eye on you. If you have 3 in less than 6 months, I'm putting you on a watch list for special actions to be taken. I want to know how many people I'm keeping an eye on each day.

       

      My data looks like this:

      CustomerVisit 1 DateVisit 2 DateVisit 3 DateCalculated Field
      Joe Bloggs(Some Date)(Another date within 6 months after V1)

      (The next job within 6 months after V1. Left blank if there is no 3rd jobs within 6 months of Visit 1 Date)

      Is 1 if [Visit 3 Date] is not null and today() - [Visit 1 Date] <180, else 0

       

      My real problem is charting this. Since the calculated field relies on today() I want to have date as the x-axis and the y-axis would have the CNTD(Customer) where Calculated Field = 1 for that customer on that date.

       

       

      I did not create this data set. I think it'd be easier if each visit was on one line, right? Right now moving [Visit 1 Date] to Columns and Sum(Calculated Field) to Rows just sums the calculated field for visits on that day, it does not sum all customers. I want the position on the date axis to be used in the calculated field instead of today()

       

      I wish I could attach data, but it's protected/I'm not allowed. I could try to anonymize it if it would help, but I'm betting this is a really simple problem and I'm just too novice.