Skip to main content

App Development

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

Announcements
Nov. 30th, Webinar! Do More With Qlik Cloud Catalog & Lineage: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
artempotsuray
Contributor III
Contributor III

Weekly Rolling Count

Hello everyone, 

I am trying to create a bar graph with a rolling count that is reset every week but shows the dates for an entire month. I have data from a SQL server of daily, weekly  and monthly tasks that get completed. We are trying to monitor the tasks and what days they are being completed. As of right now I can obtain a rolling count for the weekly tasks but I cant get the graph to break it down by week. 

Rolling Week.PNG

 

Because the tasks are weekly, I need the count of the completed to restart at the beginning of each week. Not to add up from previous weeks like shown in the graph above. My measure statement is the following:

RangeSum(
   Above(
      aggr(
Count({<Frequency = {'W'},[Time Stamp TPM.autoCalendar.MonthRelNo] = {0}, [Time Stamp TPM.autoCalendar.YearsAgo] = {0}>} Distinct Combo)
      ,[Time Stamp TPM.autoCalendar.Date])
   ,0,RowNo())
)

Thanks in Advance

Labels (3)
11 Replies
artempotsuray
Contributor III
Contributor III
Author

Here you go. Please refer to the data load editor if some field names are confusing as I have used created field names there.

artempotsuray
Contributor III
Contributor III
Author

For anyone who is might need this in the future. This is how i got it to work:

I added ( , Numeric, Ascending) to my aggr functions and it helped

aggr(
   RangeSum(
      Above(
         aggr(
Count({<Frequency = {'W'}, [Time Stamp TPM.autoCalendar.Month] = {'=Month(Today())'}, [Time Stamp TPM.autoCalendar.YearsAgo] = {0}>} Distinct Combo)
         , ([Time Stamp TPM.autoCalendar.Date], Numeric, Ascending))
      ,0,RowNo())
   )
,([Time Stamp TPM.autoCalendar.Week], Numeric, Ascending), ([Time Stamp TPM.autoCalendar.Date], Numeric Ascending))