Results 1 to 5 of 5

Thread: Matrix Table - Add Column to the end of the Matrix (not on every column)
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Apr 2011
    Posts
    40
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Matrix Table - Add Column to the end of the Matrix (not on every column)

    Hey Guys,
    Looking for some help here - I havent been able to find a solution anywhere.

    I have a matrix table that provides a view of aging inventory. For each client they have a number of days to work the ticket before it is outside of SLA. I want to add a column that shows when those tickets are outside of SLA. Currently when I try to add that data point, it shows up on every column, but I just want it to show at the end of the matrix (the last column).

    Example: This is what I want to see.....
    Client: 0 Days 1 Day 2 Days 3 days 4 days 5 days SLA # of Days
    A 30 tickets 20 tickets 16 Tickets 10 Tickets 6 Tickets 3 Tickets 4 days
    B 25 Tickets 18 tickets 12 tickets 8 Tickets 0 0 2 days

    Example: This is what I get currently get when trying to build the matrix (not what I want)
    Client 0 Days SLA # of Days 1 Day SLA # of Days 2 Day SLA # of Days 3 Days SLA # of Days 4 Days SLA # of Days 5 Days SLA # of Days
    A 30 Tickets 4 days 20 tickets 4 days 16 Tickets 4 days 10 Tickets 4 days 6 Tickets 4 days 3 Tickets 4 days
    B 25 Tickets 2 days 18 Tickets 2 days 12 tickets 2 days 8 Tickets 2 days 0 2 days 0 2 days

    Does anyone know of a way to build the matrix as I'm intending it to look?

    Thanks!

  2. #2
    Board Regular RasGhul's Avatar
    Join Date
    Jul 2016
    Posts
    548
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Matrix Table - Add Column to the end of the Matrix (not on every column)

    Can you provide a sample of your source data?

    - forum use guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

  3. #3
    New Member
    Join Date
    Apr 2011
    Posts
    40
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Matrix Table - Add Column to the end of the Matrix (not on every column)

    Quote Originally Posted by RasGhul View Post
    Can you provide a sample of your source data?
    I have 2 tables:
    1) A custom date table that counts the aging starting with todays date as day 0, yesterday as day 1, day before yesterday Day 2, etc. etc.
    2) Ticket data containing the: client Name, Ticket date opened, # of tickets, SLA threshold
    So in the example provided the data would look like similar to this.....
    Client Ticket Date # of tickets SLA Threshold
    A 9/24/2019 30 4
    A 9/23/2019 20 4
    A 9/22/2019 16 4
    A 9/21/2019 10 4
    A 9/20/2019 6 4
    A 9/19/2019 3 4
    B 9/24/2019 25 2
    B 9/23/2019 18 2
    B 9/22/2019 12 2
    B 9/21/2019 8 2

    This is a stripped down version of the data, but if I could get assistance with how not to duplicate the "SLA Threshold" on every column, I can then apply it to the real data/matrix visual I'm trying to create.

    Thanks!

  4. #4
    Board Regular RasGhul's Avatar
    Join Date
    Jul 2016
    Posts
    548
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Matrix Table - Add Column to the end of the Matrix (not on every column)

    Hi,

    Maybe this might suit, the dates in the first row will always show today's date minus 5 days, I added the array to populate a unique client list for the matrix sheet;

    The index & match for SLA # of days will always pickup the first match value for that client.

    Change sheet names and copy or drag these formulas as needed.

    ABCD
    1ClientTicket Date# of ticketsSLA Threshold
    2A25/09/2019304
    3A24/09/2019204
    4A23/09/2019164
    5A22/09/2019104
    6A21/09/201964
    7A20/09/201934
    8B25/09/2019252
    9B24/09/2019182
    10B23/09/2019122
    11B22/09/201982

    Ticket Data





    ABCDEFGH
    125-09-1924-09-1923-09-1922-09-1921-09-1920-09-19
    2Client:0 Days1 Day2 Days3 days4 days5 daysSLA # of Days
    3A30 Tickets20 Tickets16 Tickets10 Tickets6 Tickets3 Tickets4
    4B25 Tickets18 Tickets12 Tickets8 Tickets2

    Matrix



    Worksheet Formulas
    CellFormula
    B1=TODAY()
    C1=B1-1
    B3=IFNA(INDEX('Ticket Data'!$C$2:$C$11,MATCH(Matrix!$A3&Matrix!B$1,INDEX('Ticket Data'!$A$2:$A$11&'Ticket Data'!$B$2:$B$11,),0))&" Tickets","")
    H3=IF(A3="","",INDEX('Ticket Data'!$D$2:$D$11,MATCH(Matrix!$A3,'Ticket Data'!$A$2:$A$11,0)))

    Array Formulas
    CellFormula
    A3{=IFNA(INDEX('Ticket Data'!$A$2:$A$11,MATCH(0,COUNTIF(Matrix!$A$2:A2,'Ticket Data'!$A$2:$A$11),0)),"")}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself


    - forum use guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

  5. #5
    Board Regular
    Join Date
    Nov 2017
    Posts
    72
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Matrix Table - Add Column to the end of the Matrix (not on every column)

    Looks to me like simple pivot operation. See if this works
    Highlight your source range
    Data ... From Table/Range ... [x] my table has headers
    Change the text of the PowerQuery generated 2nd step to read {"Ticket Date", type date} instead of {"Ticket Date", type datetime} or {"Ticket Date", Int64.Type}
    Click to select Ticket Date column then Transform...Pivot Column... and for value column, choose # of tickets

    Code:
    let  Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Client", type text}, {"Ticket Date", type date}, {"# of tickets", Int64.Type}, {"SLA Threshold", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"Ticket Date", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Sorted Rows", {{"Ticket Date", type text}}, "en-US")[#"Ticket Date"]), "Ticket Date", "# of tickets", List.Sum)
    in #"Pivoted Column"

    Fancy version that rearranges columns and sets neutral column headers

    Code:
    let  Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Client", type text}, {"Ticket Date", type date}, {"# of tickets", Int64.Type}, {"SLA Threshold", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"Ticket Date", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"Ticket Date", type text}}, "en-US")[#"Ticket Date"]), "Ticket Date", "# of tickets", List.Sum),
    #"Demoted Headers" = Table.DemoteHeaders(#"Pivoted Column"),
    #"Renamed Columns" = Table.RenameColumns(#"Demoted Headers",{{"Column1", "Client"}, {"Column2", "SLA Threshold"}, {"Column3", "0 Days"}, {"Column4", "1 Day"}, {"Column5", "2 Day"}, {"Column6", "3 Day"}, {"Column7", "4 Day"}, {"Column8", "5 Day"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Client", "0 Days", "1 Day", "2 Day", "3 Day", "4 Day", "5 Day", "SLA Threshold"}),
    #"Removed Top Rows" = Table.Skip(#"Reordered Columns",1)
    in  #"Removed Top Rows"
    Last edited by horseyride; Sep 25th, 2019 at 08:36 AM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •