Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Here you go. Please refer to the data load editor if some field names are confusing as I have used created field names there.
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))