Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor III
Contributor III

Max number of days between dates

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

1 Solution

Accepted Solutions
MVP
MVP

If you want to find it in the load script this logic could be used:

2018-07-28 10_59_39-_#QC 2018-07-28 Max Number of Days between Dates _ Data load editor - Qlik Sense.png

If you want to calculate it purely in the app and not in the load script/data model:

2018-07-28 11_04_10-_#QC 2018-07-28 Max Number of Days between Dates - My new sheet _ Sheets - Qlik .png

The first measure for the KPI is:

    Max(Aggr(Only(Date)-Above(Only(Date)), Date ))

The second measure for the KPI is:

    Aggr(If( Rank(TOTAL Only(Date)-Above(Only(Date))) = 1 , Date , Null() ),Date)

In the table the measure is simply:

    If( Rank(TOTAL Only(Date)-Above(Only(Date))) = 1 , Max(Aggr( Only(Date)-Above(Only(Date)) , Date )) , Null() )

and in the properties of the table I had to turn off the checkmark for "Include zero values" like this:

2018-07-28 11_12_16-_#QC 2018-07-28 Max Number of Days between Dates - My new sheet _ Sheets - Qlik .png

View solution in original post

6 Replies
MVP
MVP

If you want to find it in the load script this logic could be used:

2018-07-28 10_59_39-_#QC 2018-07-28 Max Number of Days between Dates _ Data load editor - Qlik Sense.png

If you want to calculate it purely in the app and not in the load script/data model:

2018-07-28 11_04_10-_#QC 2018-07-28 Max Number of Days between Dates - My new sheet _ Sheets - Qlik .png

The first measure for the KPI is:

    Max(Aggr(Only(Date)-Above(Only(Date)), Date ))

The second measure for the KPI is:

    Aggr(If( Rank(TOTAL Only(Date)-Above(Only(Date))) = 1 , Date , Null() ),Date)

In the table the measure is simply:

    If( Rank(TOTAL Only(Date)-Above(Only(Date))) = 1 , Max(Aggr( Only(Date)-Above(Only(Date)) , Date )) , Null() )

and in the properties of the table I had to turn off the checkmark for "Include zero values" like this:

2018-07-28 11_12_16-_#QC 2018-07-28 Max Number of Days between Dates - My new sheet _ Sheets - Qlik .png

View solution in original post

Contributor III
Contributor III

Thank you this helped so much!

Contributor III
Contributor III

One more question actually. I was able to use the code you suggested and modify it with my parameters:

Max(Aggr(Only(Date)-Above(Only(Date)), Date ))

turned into

Max(Aggr(Only({<Formality={F}, Type={P}, Responsibility={MS,S,TW,U}, RCC={1}>}Date)-Above(Only({<Formality={F}, Type={P}, Responsibility={MS,S,TW,U}, RCC={1}>}Date)),Date))

However, how can I make this default to data only for the max year. When I deselect my 2018 filter it changes that data and looks for the the max days of no incidence over the past 4 years. I would like the number to stay as the 2018 number even when I deselect the filter. I tried a couple if statements but they just gave me null. Thanks!

MVP
MVP

You have to add Year as a Set Modifier field along with the others. And the element value for the Year should be the maximum year irrespective of any selections:

Max(

     Aggr(

          Only({<Formality={F}, Type={P}, Responsibility={MS,S,TW,U}, RCC={1}, Year={$(=Max({1} Year))}>} Date)

     -

          Above(

               Only({<Formality={F}, Type={P}, Responsibility={MS,S,TW,U}, RCC={1}, Year={$(=Max({1} Year))}>} Date)

          )

          ,

          Date

     )

)


MVP
MVP

You're welcome

Contributor III
Contributor III

Thanks! This worked.