Qlik Community

Qlik Sense App Development

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

Highlighted
artempotsuray
New 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
artempotsuray
New 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))

 

11 Replies

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

 

artempotsuray
New 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]

 

Re: Weekly Rolling Count

Is there another Week related field in your dashboard?

artempotsuray
New 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]

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])
artempotsuray
New 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. 

Re: Weekly Rolling Count

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

artempotsuray
New 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?

Re: Weekly Rolling Count

Zip the qvf file and then attach