handling dates

[bimanbeginner]

CreatedDate=2021/05/01
ClosedDate=2021/05/27
TicketID
The ticket was Open for 26 Days (Duration)

CreateDate is driving the Model

so if you view the Tickets by week which would be created week, you would only see the Duration of the ticket for the week that it has been Created

How can I view this data by the actual week. I hope this makes sense.

[rubenmarin]

Hi, a possible solution can be use set analysis in expression,something like:

Count({<CreatedDate={"<=$(=WeekStart(Today()))"}, ClosedDate={>="$(=WeekStart(Today()))"},Year,Month,Date>} TicketID)

Another solution can be setting a key for each different interval and create a relation for each interval with all the different dates it has. This can return a huge table so it will be better to go with the first option.

[bimanbeginner]

Thank you for the reply , I have actually tried in set analysis using a between created and closed date but it doesn't seem to be giving the correct answer, let me work on this some more and confirm.

[rubenmarin]

Ok, My expressio had a typo with double quotes, it should be: Count({<CreatedDate={"<=$(=WeekStart(Today()))"}, ClosedDate={">=$(=WeekStart(Today()))"},Year,Month,Date>} TicketID)

You can also do manual selections  in createddate and closed date to check the records it returns, maybe some records doesn't have a closedDate, you can to use another field for closedDate or edit the Closeddate irselft in script with: If(IsNull(ClosedDate), MakeDate(2099), ClosedDate) as ClosedDate // This sets 01/01/2099 as closed date, so the >= also filters this this records.

[bimanbeginner]

Hi Rubenmarin

So I believe my complication would come in with the dimension that I use in my table.

I am using the createdate to show how many ticket each person closed within kpi for each week, I'm using createdate to create the lastday of the week, in this case Sat, so when looking at my table chart , it would only give the reflection of the week the ticket was opened if I use the last day of the week field as a dimension.

I would need to see  for each week how many tickets were closed within KPI, so somehow I need to ignore the create date in my dimension...