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

[bolwahnn]

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.....
[TABLE="width: 500"]
<tbody>[TR]
[TD]Client:
[/TD]
[TD]0 Days[/TD]
[TD]1 Day[/TD]
[TD]2 Days[/TD]
[TD]3 days[/TD]
[TD]4 days[/TD]
[TD]5 days[/TD]
[TD]SLA # of Days[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]30 tickets[/TD]
[TD]20 tickets[/TD]
[TD]16 Tickets[/TD]
[TD]10 Tickets[/TD]
[TD]6 Tickets[/TD]
[TD]3 Tickets[/TD]
[TD]4 days[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]25 Tickets[/TD]
[TD]18 tickets[/TD]
[TD]12 tickets[/TD]
[TD]8 Tickets[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]2 days[/TD]
[/TR]
</tbody>[/TABLE]

Example: This is what I get currently get when trying to build the matrix (not what I want)
[TABLE="width: 500"]
<tbody>[TR]
[TD]Client[/TD]
[TD]0 Days[/TD]
[TD]SLA # of Days[/TD]
[TD]1 Day[/TD]
[TD]SLA # of Days[/TD]
[TD]2 Day[/TD]
[TD]SLA # of Days[/TD]
[TD]3 Days[/TD]
[TD]SLA # of Days[/TD]
[TD]4 Days[/TD]
[TD] SLA # of Days[/TD]
[TD]5 Days [/TD]
[TD]SLA # of Days[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]30 Tickets[/TD]
[TD]4 days[/TD]
[TD]20 tickets[/TD]
[TD]4 days[/TD]
[TD]16 Tickets[/TD]
[TD]4 days[/TD]
[TD]10 Tickets[/TD]
[TD]4 days[/TD]
[TD]6 Tickets[/TD]
[TD]4 days[/TD]
[TD]3 Tickets[/TD]
[TD]4 days[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]25 Tickets[/TD]
[TD]2 days[/TD]
[TD]18 Tickets[/TD]
[TD]2 days[/TD]
[TD]12 tickets[/TD]
[TD]2 days[/TD]
[TD]8 Tickets[/TD]
[TD]2 days[/TD]
[TD]0[/TD]
[TD]2 days[/TD]
[TD]0[/TD]
[TD]2 days[/TD]
[/TR]
</tbody>[/TABLE]

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

Thanks!

 

[RasGhul]

Can you provide a sample of your source data?

 

[bolwahnn]

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.....
[TABLE="width: 500"]
<tbody>[TR]
[TD]Client[/TD]
[TD]Ticket Date[/TD]
[TD]# of tickets[/TD]
[TD]SLA Threshold[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]9/24/2019[/TD]
[TD]30[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]9/23/2019[/TD]
[TD]20[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]9/22/2019[/TD]
[TD]16[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]9/21/2019[/TD]
[TD]10[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]9/20/2019[/TD]
[TD]6[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]9/19/2019[/TD]
[TD]3[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]9/24/2019[/TD]
[TD]25[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]9/23/2019[/TD]
[TD]18[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]9/22/2019[/TD]
[TD]12[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]9/21/2019[/TD]
[TD]8[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

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!

 

[RasGhul]

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.

Book1
	A	B	C	D
1	Client	Ticket Date	# of tickets	SLA Threshold
2	A	25/09/2019	30	4
3	A	24/09/2019	20	4
4	A	23/09/2019	16	4
5	A	22/09/2019	10	4
6	A	21/09/2019	6	4
7	A	20/09/2019	3	4
8	B	25/09/2019	25	2
9	B	24/09/2019	18	2
10	B	23/09/2019	12	2
11	B	22/09/2019	8	2
Ticket Data

Book1
	A	B	C	D	E	F	G	H
1		25-09-19	24-09-19	23-09-19	22-09-19	21-09-19	20-09-19
2	Client:	0 Days	1 Day	2 Days	3 days	4 days	5 days	SLA # of Days
3	A	30 Tickets	20 Tickets	16 Tickets	10 Tickets	6 Tickets	3 Tickets	4
4	B	25 Tickets	18 Tickets	12 Tickets	8 Tickets			2
Matrix

Cell Formulas
Range	Formula
B1	=TODAY()
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","")
C1	=B1-1
H3	=IF(A3="","",INDEX('Ticket Data'!$D$2:$D$11,MATCH(Matrix!$A3,'Ticket Data'!$A$2:$A$11,0)))
A3	{=IFNA(INDEX('Ticket Data'!$A$2:$A$11,MATCH(0,COUNTIF(Matrix!$A$2:A2,'Ticket Data'!$A$2:$A$11),0)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[horseyride]

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

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

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"