Qlik Community

Ask a Question

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

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 consecutive null value days in the year

Hi guys,

I have a bar graph showing some data about incidents on each day. I made this using :

=Count({<Formality={F}, Type={P}, Responsibility={MS,S,TW,U}, RCC={1}>}No)

Formality, Type, Responsibility RCC, and No are all fields. I did this to preset the filters I needed with  the data instead of just graphing dates against all the incidents.

I'm trying to calculate the max number of consecutive zero incident days in the current year and cant figure out a good solution. Any help would be appreciated. Thanks!

7 Replies
Specialist II
Specialist II

Hi,

here is an expression, hope may help you :

where tot is number of consecutives 0 :

= if(nb <>0,0,

   if(above(nb)=0,above(tot)+1,1))

Contributor III
Contributor III

is tot a variable I have to make?

Contributor III
Contributor III

I assume nb is the field with my data

Specialist II
Specialist II

yes nb is your field which give count of incident,

tot is the name of the expression

Contributor III
Contributor III

This expression does not work for me. It returns null

Specialist II
Specialist II

could you share some data please,

and what is your expression ?, it's may be this too

Contributor III
Contributor III

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