Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
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)
1 Solution

Accepted Solutions
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))

 

View solution in original post

11 Replies
sunny_talwar

Try this

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

 

artempotsuray
Contributor III
Contributor III
Author

How would I derive the following in my autocalendar that is created by the script. I am missing this so I cant try it just yet because of this reason

[Time Stamp TPM.autoCalendar.WeekYear]

 

sunny_talwar

Is there another Week related field in your dashboard?

artempotsuray
Contributor III
Contributor III
Author

Yes, I have the following

[Time Stamp TPM.autoCalendar.Week]

[Time Stamp TPM.autoCalendar.WeekRelNo]

[Time Stamp TPM.autoCalendar.WeeksAgo]

sunny_talwar

I am not sure what WeekRelNo is, but may be try this

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

or this

Aggr(
RangeSum( Above( Count({<Frequency = {'W'},[Time Stamp TPM.autoCalendar.MonthRelNo] = {0}, [Time Stamp TPM.autoCalendar.YearsAgo] = {0}>} Distinct Combo) , 0, RowNo()) )
, [Time Stamp TPM.autoCalendar.Year], [Time Stamp TPM.autoCalendar.Week], [Time Stamp TPM.autoCalendar.Date])
artempotsuray
Contributor III
Contributor III
Author

.WeekRelNo is similar to .Week. 

However I tried both ways that you proposed and I received the following graph

Rolling Week.PNG

It seems like the first date of each week is getting the correct count with the exception of 1/8/2019 but this isn't exactly what I need. I need it to look more like the graph posted in the initial question where it stacks up throughout the week. 

sunny_talwar

 Would you be able to share a sample to check this out?

artempotsuray
Contributor III
Contributor III
Author

 

How would I add my qvf file as an attachment? It is not allowing me to do so and gives me the following error:

"The contents of the attachment doesn't match its file type." 

Do i need to change it to a qvw? Or perhaps another type?

sunny_talwar

Zip the qvf file and then attach