Create snap shot chart from the current data

[#1]

Hello all,

I have a contract table consisting following columns:

Contract_Id,

Contract_Name,

Contract_startdate,

Contract_enddate,

Contract_status

Based on the start and end date, the contract status will be 'Active', 'Expired' or 'Future'.

I would like to create a chart in which my dimension axes will be time dimension and would like to show how many 'Active' contracts for each time frame.

Please look at the example data below

contarct_id	contract_name	contract_startdate	contract_enddate	contract_status
1	abc	1/1/2012	12/31/2015	Expired
9	ijk	2/1/2012	1/31/2016	Expired
16	pqr	3/3/2013	3/2/2015	Expired
5	efg	4/1/2013	3/31/2018	Active
19	stu	4/3/2013	4/2/2014	Expired
7	ghi	5/31/2013	5/30/2016	Active
8	hij	8/1/2013	7/31/2018	Active
12	lmn	1/1/2014	12/31/2015	Expired
2	bcd	3/10/2014	3/9/2017	Active
3	cde	4/4/2014	4/3/2016	Expired
18	rst	6/1/2014	5/31/2015	Expired
6	fgh	7/1/2014	6/30/2017	Active
14	nop	9/3/2014	9/2/2017	Active
15	opq	12/21/2014	12/20/2016	Active
13	mno	3/3/2015	3/2/2016	Expired
17	qrs	5/19/2015	5/18/2017	Active
11	klm	6/5/2015	6/4/2016	Active
4	def	6/1/2016	5/31/2019	Future
10	jkl	8/1/2016	7/31/2019	Future
20	tuv	9/2/2016	9/1/2019	Future

Based on this data, I would like to create a bar chart showing how many 'Active' contracts within each time frame i.e. if we show by Month than how many 'Active' contract in that month at that time.

Please see below for the expected result table:

Month	# of Active Contract in that month at that time	explanation
Jan 2012	1	1
Feb 2012	2	2
Mar 2012	2	2
Apr 2012	2	2
May 2012	2	2
Jun 2012	2	2
Jul 2012	2	2
Aug 2012	2	2
Sep 2012	2	2
Oct 2012	2	2
Nov 2012	2	2
Dec 2012	2	2
Jan 2013	2	2
Feb 2013	2	2
Mar 2013	3	3
Apr 2013	5	5
May 2013	6	6
Jun 2013	6	6
Jul 2013	6	6
Aug 2013	7	7
Sep 2013	7	7
Oct 2013	7	7
Nov 2013	7	7
Dec 2013	7	7
Jan 2014	8	8
Feb 2014	8	8
Mar 2014	9	9
Apr 2014	9	(10-1) = 9 //-1 for one getting expired in Apr 2014
May 2014	9	9
Jun 2014	10	10
Jul 2014	11	11
Aug 2014	11	11
Sep 2014	12	12
Oct 2014	12	12
Nov 2014	12	12
Dec 2014	13	13
Jan 2015	13	13
Feb 2015	13	13
Mar 2015	13	(14-1) = 13 //-1 for one getting expired in Mar 2015
Apr 2015	13	13
May 2015	13	(14-1) = 13 //-1 for one getting expired in May 2015
Jun 2015	14	14
Jul 2015	14	14
Aug 2015	14	14
Sep 2015	14	14
Oct 2015	14	14
Nov 2015	14	14
Dec 2015	12	(14-2) = 12 //-2 for two getting expired in Dec 2015
Jan 2016	11	(12-1) = 11 //-1 for one getting expired in Jan 2016
Feb 2016	11	11
Mar 2016	10	(11-1) = 10 //-1 for one getting expired in Mar 2016
Apr 2016	9	(10-1) = 9 //-1 for one getting expired in Apr 2016
May 2016	8	(9-1) = 8 //-1 for one getting expired in May 2016

Greatly appreciate any help you can provide.

Thank you,

Parth Shah

[swuehl · Accepted solution]

Create reference dates for your intervals, then use these reference dates as timeline

Creating Reference Dates for Intervals

(reference dates don't need to be down to date granularity, maybe monthstart would be enough)

[santiago_respane]

Hi Parth,

First you need some kind of calendar or references as Swuehl mentioned,

where for each date you will have its respective month, quarter, year, etc.

And then in your chart add desired period as dimension and in expression add like this:

=COUNT({<contract_status={'Active'}>} contract_id)

If you want you can add a second expression with Expired ones:

=COUNT({<contract_status={'Expired'}>} contract_id)

And transpose them to compare.

Let me know if this helps.

Kind regards,

[#4]

Thank you Swuehl. This is amazing.

Regards,

Parth

[#5]

Hi Santiago,

Thank you for your response. After creating those reference dates I used them as my dimension axes in the chart.

For my expression, I did what you mentioned.

By doing that, it only took 'Active' contracts and didnt take any contracts which were 'Active' before but are 'Expired' now. I would like to show those contracts too which were 'Active' before and are 'Expired' now with in their time range.

I guess, if I dont provide this condition in the expression; than I get what I want?

Thank you,

Parth

[swuehl]

If you have created reference dates only for the period they are active, you probably don't need to do anything special, just do a DISTINCT count of your contracts.

[#7]

Hi Swuehl,

I didnt do anything but by following the approach from the link you posted, it automatically created reference dates for a contract when it was active.

I guess I am all set. Thank you guys. I appreciate your help.

Regards,

Parth

[ziadm]

Hi Parth

You have to created Month and Year of your Dates and make these as your dimension and the expression will be count of the active and expired contract

check the attached

if this solve the problem please mark as correct or helpful

Thanks