Forum Discussion

Folio's avatar
Folio
Regular Visitor
8 years ago
Solved

Cumulative Difference with Categorical Variables

I am trying to create a clustered bar chart showing count of closed and open tickets each month. That's the easy part. I want to add a line to the chart showing the cumulative difference between the ...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    Hi Folio,

     

    I'd like to suggest you use below formula to expand your original table, then you can direct use detail date range table to create visuals.

     

    Expand Table = 
VAR _calendar =
    CALENDAR ( MINX ( Sheet1, [Created_Dt] ), MAXX ( Sheet1, [Resolved_Dt] ) )
RETURN
    SELECTCOLUMNS (
        FILTER (
            CROSSJOIN ( Sheet1, _calendar ),
            [Date] >= [Created_Dt]
                && [Date] <= [Resolved_Dt]
        ),
        "Ticket Id", [Ticket Id],
        "Ticket Status", [Ticket Status],
        "Date", [Date]
    )

     

    Regards,

    Xiaoxin Sheng

     

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hi Folio,

 

I'd like to suggest you use below formula to expand your original table, then you can direct use detail date range table to create visuals.

 

Expand Table = 
VAR _calendar =
    CALENDAR ( MINX ( Sheet1, [Created_Dt] ), MAXX ( Sheet1, [Resolved_Dt] ) )
RETURN
    SELECTCOLUMNS (
        FILTER (
            CROSSJOIN ( Sheet1, _calendar ),
            [Date] >= [Created_Dt]
                && [Date] <= [Resolved_Dt]
        ),
        "Ticket Id", [Ticket Id],
        "Ticket Status", [Ticket Status],
        "Date", [Date]
    )

 

Regards,

Xiaoxin Sheng