topic Re: Weekly Rolling Count in App Development

Weekly Rolling Count

artempotsuray — Sat, 16 Nov 2024 21:30:38 GMT

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

Re: Weekly Rolling Count

sunny_talwar — Tue, 29 Jan 2019 18:13:36 GMT

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

Re: Weekly Rolling Count

artempotsuray — Tue, 29 Jan 2019 18:16:54 GMT

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

sunny_talwar — Tue, 29 Jan 2019 18:17:53 GMT

Is there another Week related field in your dashboard?

Re: Weekly Rolling Count

artempotsuray — Tue, 29 Jan 2019 18:20:22 GMT

Yes, I have the following

[Time Stamp TPM.autoCalendar.Week]

[Time Stamp TPM.autoCalendar.WeekRelNo]

[Time Stamp TPM.autoCalendar.WeeksAgo]

Re: Weekly Rolling Count

sunny_talwar — Tue, 29 Jan 2019 18:30:18 GMT

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

Re: Weekly Rolling Count

artempotsuray — Tue, 29 Jan 2019 18:40:03 GMT

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

Re: Weekly Rolling Count

sunny_talwar — Tue, 29 Jan 2019 18:52:52 GMT

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

Re: Weekly Rolling Count

artempotsuray — Tue, 29 Jan 2019 19:20:13 GMT

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

sunny_talwar — Tue, 29 Jan 2019 19:22:59 GMT

Zip the qvf file and then attach

Re: Weekly Rolling Count

artempotsuray — Tue, 05 Feb 2019 20:12:30 GMT

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

Re: Weekly Rolling Count

artempotsuray — Tue, 26 Feb 2019 15:33:46 GMT

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