Count where creation date is before X in combo chart

[orangebloss]

Hi

I have the following chart which counts the creation date (Opened) and completed date (Closed) in the YY/WW it occurred.

I'd like to add a line to show how many were open in total in that week i.e. the creation date is <= the max date for the YYWW and completion date> the max date for YYWW

24/35 includes the dates 27th August - 1st September so would need to count anything raised and not closed in that week + any not closed from 24/34 etc

e.g.

5 open 2 closed by max date 24/34 (3 open charted on graph)

2 open 3 closed by max date 24/35 (2 open charted on graph)

4 open 1 closed by max date 24/36 (5 open charted on graph)

[stevedark]

Hi @orangebloss 

To be able to count all open tickets you will need to create rows for each date (or week, if that is the only granularity you need.

So, if your table has fields like this, you need to look to create the rows inbetween: 

Source:
LOAD
    ID,
    Date#(OpenDate, 'YYYY-MM-DD') as OpenDate,
    Date#(CloseDate, 'YYYY-MM-DD') as CloseDate
INLINE [
ID,OpenDate,CloseDate
10001,2024-09-12,2024-10-03
10002,2024-09-27,2024-10-13
10002,2024-10-01,
];

You can do this with WHILE and ITER() statements:

Tickets:
LOAD
    *,
    Date(WeekStart(Date), 'DD MMM YYYY') as Week
    ;
LOAD
    ID,
    Date(OpenDate + (IterNo()-1), 'DD MMM YYYY') as Date,
    If(IterNo() = 1, 1, 0) as OpenCount,
    if(OpenDate + (IterNo()-1) = CloseDate, 1, 0) as CloseCount,
    1 as DaysOpen,
    Date(OpenDate, 'DD MMM YYYY') as [Open Date],
    Date(CloseDate, 'DD MMM YYYY') as [Close Date]
    WHILE OpenDate + (IterNo()-1) <= if(alt(CloseDate,0) = 0, today(), CloseDate)
    ;
LOAD
    *
RESIDENT Source;

DROP TABLE Source;

The end result is a row for every day that a ticket is open for. You therefore need to count tickets as COUNT(DISTINCT ID). There are separate columns for counting opens and closes in the correct day or week.

The data look like this:

This can be aggregated down by ID:

By week like this:

And that can be charted like this:

I hope that all makes sense?

I've attached the Sense app I used to test this. I hope that is useful also.

Steve

[orangebloss]

This is amazing thank you! I'll see if I can implement it!