Chart for approaching Due Dates

%3CLINGO-SUB%20id%3D%22lingo-sub-1859259%22%20slang%3D%22en-US%22%3EChart%20for%20approaching%20Due%20Dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1859259%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20following%20columns%20in%20my%20data%20and%20would%20like%20to%20answer%20these%20question%20by%20creating%20a%20chart%20out%20of%20it%3A%3C%2FP%3E%3CP%3EQuestions%3A%26nbsp%3B%3C%2FP%3E%3COL%3E%3CLI%3ENeed%20a%20chart%20which%20shows%20How%20many%20tickets%20are%20approaching%20due%20dates%3F%3C%2FLI%3E%3CLI%3EDescending%20list%20of%20tickets%20approaching%20due%20dates%20i.e%20tickets%20with%20less%20days%20to%20due%20dates%20to%20tickets%20with%20more%20days%20to%20due%20dates%3F%3C%2FLI%3E%3CLI%3EHow%20and%20which%20tickets%20have%20already%20crossed%20the%20due%20date%3F%3C%2FLI%3E%3CLI%3EHow%20and%20which%20tickets%20have%20due%20date%20today%3F%3C%2FLI%3E%3C%2FOL%3E%3CTABLE%20border%3D%221%22%20width%3D%22100%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2225%25%22%20height%3D%2229px%22%3ETicket%20ID%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2229px%22%3ETicket%20Type%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2229px%22%3ECreated%20Date%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2229px%22%3EDue%20Date%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2225%25%22%20height%3D%2229px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2229px%22%3EProject%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2229px%22%3E09%2F02%2F20%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2229px%22%3E11%2F10%2F2020%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2225%25%22%20height%3D%2229px%22%3E2%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2229px%22%3ETask%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2229px%22%3E09%2F03%2F20%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2229px%22%3E11%2F10%2F2020%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2225%25%22%20height%3D%2229px%22%3E3%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2229px%22%3EIncident%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2229px%22%3E09%2F03%2F20%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2229px%22%3E10%2F3%2F2020%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2225%25%22%20height%3D%2229px%22%3E4%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2229px%22%3EIncident%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2229px%22%3E09%2F03%2F20%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2229px%22%3E10%2F15%2F2020%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2225%25%22%20height%3D%2229px%22%3E5%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2229px%22%3EProject%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2229px%22%3E09%2F04%2F20%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2229px%22%3E11%2F13%2F2020%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2225%25%22%20height%3D%2229px%22%3E6%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2229px%22%3ETask%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2229px%22%3E09%2F04%2F20%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2229px%22%3E10%2F27%2F2020%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2225%25%22%20height%3D%2229px%22%3E7%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2229px%22%3EProject%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2229px%22%3E09%2F04%2F20%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2229px%22%3E11%2F05%2F2020%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E*%3CEM%3Elast%20ticket%20has%20due%20date%20as%26nbsp%3B%20of%20today.%26nbsp%3B%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20you%20please%20me%20with%20this%3F%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F93699%22%20target%3D%22_blank%22%3E%40Rajesh-S%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1859259%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1860228%22%20slang%3D%22en-US%22%3ERe%3A%20Chart%20for%20approaching%20Due%20Dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1860228%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F853217%22%20target%3D%22_blank%22%3E%40JayJoshi%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3ETo%20fix%20the%20issue%20you%20need%20to%20summarize%20the%20data%20%3CSPAN%3Eb%3C%2FSPAN%3Ey%20using%20one%20Helper%20Column%2C%20and%20the%20name%20given%20to%20it%20is%20STATUS.%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Rajesh-S_0-1604654552695.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F231860i2D7F3A604D6B72AB%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Rajesh-S_0-1604654552695.png%22%20alt%3D%22Rajesh-S_0-1604654552695.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3EHow%20it%20works%3A%3C%2FSTRONG%3E%3C%2FP%3E%3CUL%3E%3CLI%3EFormula%20in%20cell%20Y2%3A%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DIF(X2%26gt%3BTODAY()%2C%22Due%20Date%20Approachig%22%2CIF(X2%3DTODAY()%2C%22Due%20Today%22%2C%22Due%20Date%20Crossed%22))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3ECreate%20Pivot%20Tale.%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%3CEM%3EWhere%20Ticket%20Type%20in%20Row%2C%20Status%20in%20Column%20and%20Due%20Date%20are%20as%20Value%20(Count).%3C%2FEM%3E%3C%2FP%3E%3CUL%3E%3CLI%3ENow%20create%20a%20Com%3CSPAN%3Ebo%26nbsp%3B%3C%2FSPAN%3EPivot%20Chart.%3C%2FLI%3E%3C%2FUL%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Rajesh-S_0-1604655321233.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F231863iFBB434A52984F79D%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Rajesh-S_0-1604655321233.png%22%20alt%3D%22Rajesh-S_0-1604655321233.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%223%22%3E%3CSTRONG%3EN.%3CSPAN%3EB.%3C%2FSPAN%3E%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FP%3E%3CUL%3E%3CLI%3E%3CFONT%20size%3D%223%22%3E%3CSPAN%3EBoth%20PT%20%26amp%3B%20Pivot%20Chart%20can%20%3CSPAN%3Eb%3C%2FSPAN%3Ee%20filtered%20on%20Ticket%20Type%20and%20Status.%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

I have following columns in my data and would like to answer these question by creating a chart out of it:

Questions: 

  1. Need a chart which shows How many tickets are approaching due dates?
  2. Descending list of tickets approaching due dates i.e tickets with less days to due dates to tickets with more days to due dates?
  3. How and which tickets have already crossed the due date?
  4. How and which tickets have due date today?
Ticket IDTicket TypeCreated DateDue Date
1Project09/02/2011/10/2020
2Task09/03/2011/10/2020
3Incident09/03/2010/3/2020
4Incident09/03/2010/15/2020
5Project09/04/2011/13/2020
6Task09/04/2010/27/2020
7Project09/04/2011/05/2020 

*last ticket has due date as  of today. 

 

Can you please me with this?

@Rajesh-S 

1 Reply

@JayJoshi 

 

To fix the issue you need to summarize the data by using one Helper Column, and the name given to it is STATUS.

 

Rajesh-S_0-1604654552695.png

How it works:

  • Formula in cell Y2:

 

=IF(X2>TODAY(),"Due Date Approachig",IF(X2=TODAY(),"Due Today","Due Date Crossed"))

 

  • Create Pivot Tale.

Where Ticket Type in Row, Status in Column and Due Date are as Value (Count).

  • Now create a Combo Pivot Chart.
 

Rajesh-S_0-1604655321233.png

 

N.B.

  • Both PT & Pivot Chart can be filtered on Ticket Type and Status.