Three issues on the Dashboard
- On Show Hours Year/Weeks, need to sort by year/week (yw) Field
- On Show Week Option The add of the Last 2 and 4 Weeks don't give me correct sum.
- On graphs need group by year/Week.
Thanks hope help.
John on Table Print Hours' tab Crosstab there are interesting crosstab develop by @ Mirko.
16 replies
-
For the field called Formula under Show Hours - Years / Week you can change the code to:
let yweeks := for i in numbers('Select Year/Week') do first((select 'History Hours')[number(Id) = i].yw) end; let result := for j in sort(yweeks) do j + " " + sum((select 'History Hours')[yw = j].'Hours:') end; join(result, " ")
to keep the results in chronological order.
-
said:
On Show Week Option The add of the Last 2 and 4 Weeks don't give me correct sum.Just to double check, will you have moments when your last 2 or 4 weeks will spread across years? Meaning you have work at the beginning of the year and want to count to last one, two or three weeks of the previous year.
-
You can try this under the 2 week case:
switch true do case week(today()) = 1: (vInfo := text(sum(base[YEAR = year(today() - 1) and 'Week:_' = 52 and (YEAR = year(today()) or 'Week:_' > week(1))].'Hours:'))) default: (vInfo := text(sum(base[YEAR = year(today()) and 'Week:_' = week(today()) - 1 or 'Week:_' = week(today())].'Hours:'))) end
This takes into account for the 1st week of the year.
You can take this idea and expand it to the 4 week case.
-
Trying to use the power of Ninox date fields and looking at your structure you can simplify the code. Ninox does a very good job of adding a subtracting days. So looking at your db and in History Hours, I noticed that you use the date from the relationship with N1_Staff Hours. So I created a new formula field (called hDate) and point it to:
'N1_Staff Hours'.Date
Then in the dashboard you can simplify the Trigger code to:
case "Last 2 Weeks": (vInfo := text(sum(base[hDate >= today() - 7 and hDate <= today()].'Hours:'))) case "Last 4 Weeks": (vInfo := text(sum(base[hDate >= today() - 21 and hDate <= today()].'Hours:')))
Ninox knows how to subtract days and will follow it to the previous year so we don't have to try to figure out if we are in a previous year or not. Ninox does it for us.
I don't do graphs so hopefully someone else can jump in.
-
Or if you don't want to create a new field you can use the yw field in History Hours.
case "Last 2 Weeks": (vInfo := text(sum(base[yw >= yearweek(today() - 7) and yw <= yearweek(today())].'Hours:'))) case "Last 4 Weeks": (vInfo := text(sum(base[yw >= yearweek(today() - 21) and yw <= yearweek(today())].'Hours:')))
wasn't sure this would work, but it does. :)
Content aside
- Status Answered
- 1 yr agoLast active
- 16Replies
- 114Views
-
2
Following