0

Running total every 5 days

I want to sum expanse Sunday to Friday in work week expanse field and ignore Saturday to Sunday I can only sum per week by using week function can’t sum 5 days.

5 replies

null
    • Fred
    • 2 yrs ago
    • Reported - view

    Just to be clear, sounds like you want all days of the week except Saturday.

    You can try either of the following formulas:

    1) weekdayIndex(datefield) - will return a number with Monday = 0 - Sunday =6

    or

    2) weekdayName(datefield) - will return "Monday" - "Sunday"

    Then you can just exclude 5 or "Saturday"

      • subzwari
      • 2 yrs ago
      • Reported - view

      Fred thanks for your quick response, yes, but for next day I want to exclude previous Monday and add next week Monday and so on drop previous Tuesday and add next week Tuesday so every day I get previous 5 days sum but excluding weekend that fall in between. I think if I add date with your suggestion and somehow combine it in nested if statement it should work but will be messy if you can guide me to some other simple formula that could work.

    • Fred
    • 2 yrs ago
    • Reported - view

    Is this close to what you had in mind?

    If the DB doesn't open to the proper table, open the Dashboard table. You will see a Date field and a formula field.

    Select a date between Aug 8 and Aug 21 and you will the record Id of the last 5 records from that date. The records are from Table1.

    last5Dates.ninox
    • John_Halls
    • 2 yrs ago
    • Reported - view

    You could try this

    let d := Date;
sum((select Table1)[Date >= d - 7 and Date <= d and weekday(Date) < 5].Number)

    Regards John

      • subzwari
      • 2 yrs ago
      • Reported - view

      Thanks, For pointing me in the right direction 

Login to reply

Content aside

  • Status Answered
  • 2 yrs agoLast active
  • 5Replies
  • 108Views
  • 3 Following