Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Calculated table with all dates between two dates

Hi All,   I am trying to create a calculated table that lists all tickets and a record for each day that it was opened based on the Start Date and End Date columns. If the End Date is blank (i.e. t...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    FYI - I pieced together some google searches and was able to do what I needed in the query editor:

    1. Duplicated my source table
    2. Created a custom column with today's date using the following: DateTime.LocalNow() as datetime
    3. Changed that column to a date type instead of datetime
    4. Added another custom column using: if [Closed Date] = null then { Number.From([Opened Date])..Number.From([Today]) } else { Number.From([Opened Date])..Number.From([Closed Date]) }
    5. Expanded the column to rows
    6. Changed to a Date type
    7. Removed all the remaining columns I didn't need.
Anonymous's avatar
Anonymous
Not applicable
8 years ago

FYI - I pieced together some google searches and was able to do what I needed in the query editor:

  1. Duplicated my source table
  2. Created a custom column with today's date using the following: DateTime.LocalNow() as datetime
  3. Changed that column to a date type instead of datetime
  4. Added another custom column using: if [Closed Date] = null then { Number.From([Opened Date])..Number.From([Today]) } else { Number.From([Opened Date])..Number.From([Closed Date]) }
  5. Expanded the column to rows
  6. Changed to a Date type
  7. Removed all the remaining columns I didn't need.