1 Reply Latest reply on Nov 12, 2018 4:28 PM by Dan Cory

    How could I show up a classification, considering consecutive records by date?

    Alan Gutierrez
    Alan Gutierrez Nov 11, 2018 4:33 PM

      Hello everyone, nice day!

       

      Issue Descrption:

       

      I'm developing an analysis of a set of data with records of differente sites and making a clasification of them.

       

      I've a clasiffication with two tipes of sites:

       

      3.1.PNG

       

      Fixed Site: When a site (Identified with "ID Sitio" field) has 4 Diesel records or more.

      Eventual Site: When a site has less than 4 Diesel Records.

       

      I have that logic on different formules and it works right. My file clasificated the fixed and eventual sites, but it happens no matter if the records has two or three mounths of diffenrence between each other. Now I need that the classification can consider if the records has a a consecutive date between each other (by month).

       

      Fields:

       

      Period: Date

      ID Sitio: Site

      Sub Cost: Diesel Classification

       

      For example:

       

      A site wicht is called SINCUL1086 has 4 Diesel records in consecutive dates: July 2018, August 2018, September 2018 and Oct 2018 using the logic that I have already done and the logic that I need to figure out, it will be a fixed site.

      in the other hand if there was a site wich is called PUEPUE1290 and it has 4 Diesel records but the dates are: July, 2018, August, 2018, September 2018 and Decembre 2018 it won't be on the fixed site classification due to the dates are not consecutive.

       

      Formule (Development):

       

      Formule to determinate the site classification (If a site is Fixed or Eventual):

       

      IF {FIXED [Id Sitio]:SUM(IF [Sub Cost] = "Diesel" THEN 1 ELSE 0 END)} >= 4 THEN "FIXED" ELSE "EVENTUAL" END

      3.PNG

       

      I don't know how and where I can put that logic on my formules. I hope someone can give me a hand this time.

       

      Thanks for reading bodys.

       

      By the way, I attached the input file and the tableau file because I can't make attached as package, it's to bigger than the limits.

        • 1. Re: How could I show up a classification, considering consecutive records by date?
          Dan Cory
          Dan Cory Employee Nov 12, 2018 4:28 PM (in response to Alan Gutierrez)

          Your data doesn't actually have the behavior you describe for SINCUL1086 or PUEPUE1290 which makes it hard to test any solution.

           

          The basic idea is that you can check if the length of the range equals the number of distinct values. So you get something like this:

          IF {FIXED [Id Sitio]:COUNTD(IF [Sub Cost] = "Diesel" THEN [Period] END)} >= 4

          AND {FIXED [Id Sitio]:COUNTD(IF [Sub Cost] = "Diesel" THEN [Period] END)}=1+DATEDIFF('month',{FIXED [Id Sitio]:MIN(IF [Sub Cost] = "Diesel" THEN [Period] END)},{FIXED [Id Sitio]:MAX(IF [Sub Cost] = "Diesel" THEN [Period] END)}

          THEN "FIXED" ELSE "EVENTUAL" END