6 Replies Latest reply: Jul 30, 2018 1:29 PM by Ariana Ford RSS

    Max number of days between dates

    Ariana Ford

      I've decided to take a different approach to the question I posted previously that I think will give me the result I want. After looking at it for awhile I realized my data has no 0 values so of course it would be impossible for me to find the max number of consecutive zero days. Instead I've wrote an algorithm in pseudo-code that I think should work but am having a hard time converting it to qlik syntax. This is all to help me find the max days in the current year without an incident.

       

      1. Create variable dummyvariable1 and set equal to zero.

      2. Create variable dummyvariable2 and set equal to zero.

      3. Find first date with value. I made something that works for this:

      daynumberofyear(FirstSortedValue( Date,aggr(max({<Type={P}, RCC={1}, Responsibility={MS,S,TW,U}, Formality={F}>}Date),Date),1))

      4. Find 2nd date with value

      5.Subtract 2nd dates daynumberofyear from 1st dates daynumberofyear and store in dummyvariable1.

      6. Find next date with a value and subtract its daynumberofyear from previous day with a value. Store this in dummyvariable2.

      7. If dummyvariable2>dummyvariable1 then dummyvariable1 gets the value of dummyvariable2.

      8. Find next date with value and subtract its day from the previous dates day with a value. Store this in dummyvariable2.

      9. If dummyvariable2>dummyvariable1 then dummyvariable1 gets the value of dummyvariable2.

      10. Repeat

       

       

      Some sample data:

       

        Date                  Value

      6/12/18                   1

      6/13/18                   1

      6/21/18                   2

      6/22/18                   1

      6/26/18                   1

      7/04/18                   1

      7/16/18                   3

      7/17/18                   1

      7/23/18                   1

      7/24/18                   1