Weekly Rolling Count

[artempotsuray]

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. 

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

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

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]

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]

.WeekRelNo is similar to .Week. 

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

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]

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