I am using Qlik sense 3.0 SR3 enterprise version. I Want to create a report of Writers (Practitioners/Doctors) who wrote most consecutive days in a given time period.
For example, for month of Jan-2017 (31 days), a Practitioner A prescribed for 6 consecutive Days (Jan-1st to Jan-6th) and there was a gap of 4 days (Jan-7th to Jan-10th) and then again prescribed for another 9 consecutive days (Jan-11th to Jan-19th) and with another gap of 12 days (Jan-20th to Jan-31st). So for Jan-2017, Practitioner A wrote Max of (6, 9 ) as 9 days consistently.
Practitioner B wrote for 14 consecutive days (Jan-1st to Jan-14th), followed by a gap of gap of 12 days (Jan-15th to Jan-26th) and wrote for another 4 days (Jan-27th to Jan-31st) consecutively. So for Jan-2017, Practitioner B wrote max of (14,4) as 14 days consistently.
Consecutive days should respect time filter we select (i.e. within 1 Week, 2 weeks, 3 weeks, a month, a quarter or a year etc..). So if user selects a week 1 or week 2 or week 3 or a month or a quarter or a year no practitioners are over 7, 14, 21, (as many days in month), 90 days, 365 consecutive days respectively.
I am thinking this needs to occur on the fly in expression. Any help would be appreciated. Thanks.
