Qlik Community

Qlik Sense App Development

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

cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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 (4)
1 Solution

Accepted Solutions
Highlighted
Contributor III
Contributor III

Re: Weekly Rolling Count

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
Highlighted

Re: Weekly Rolling Count

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])

 

Highlighted
Contributor III
Contributor III

Re: Weekly Rolling Count

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]

 

Highlighted

Re: Weekly Rolling Count

Is there another Week related field in your dashboard?

Highlighted
Contributor III
Contributor III

Re: Weekly Rolling Count

Yes, I have the following

[Time Stamp TPM.autoCalendar.Week]

[Time Stamp TPM.autoCalendar.WeekRelNo]

[Time Stamp TPM.autoCalendar.WeeksAgo]

Highlighted

Re: Weekly Rolling Count

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])
Highlighted
Contributor III
Contributor III

Re: Weekly Rolling Count

.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. 

Highlighted

Re: Weekly Rolling Count

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

Highlighted
Contributor III
Contributor III

Re: Weekly Rolling Count

 

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?

Highlighted

Re: Weekly Rolling Count

Zip the qvf file and then attach