Set analysis Date Range

[tyagishaila]

Dear Community members,

I have these two fields

Created Date	Close Date
1-Oct-16	1-Oct-16
2-Oct-16	2-Oct-16
3-Oct-16
4-Oct-16
5-Oct-16	7-Oct-16
6-Oct-16
7-Oct-16	7-Oct-16
8-Oct-16

And expected result is

	Total	Open	Close
1-Oct-16	1	0	1
2-Oct-16	2	0	2
3-Oct-16	3	1	2
4-Oct-16	4	2	2
5-Oct-16	5	3	2
6-Oct-16	6	4	2
7-Oct-16	7	3	4
8-Oct-16	8	4	4

means for every date , date range start with 1-Oct-16.

Total:     suppose we check for 6-Oct-16 than total created date must be the total of 1st to 6th.

Open

[tyagishaila]

Thanks a ton.

[tyagishaila]

Hi Sunny,

I have further requirements.

Noe I need to show all open cases in bucket.

Open cases for 1 to 3 days

Open cases for 4 to 7 days

Open cases for 8 to 16 days

Open cases for 16 to 30 days

Open cases for more than 30 days.

For example:  for month Aug

Open cases for 1 to 3 days means cases open before 3 days.

i.e. open cases between date 29-aug-16 to 31-aug-16.

Thanks

[sunny_talwar]

tyagishaila‌ -

I think its better you open a new thread for a new set of requirement as it would be difficult for others in the future to track down what this thread was all about. In addition, you will get more set of eyes by creating a new thread.

Best,

Sunny

[tyagishaila]

Okay

[tyagishaila]

Actually I am not able to make it a new thread,

Sorry I have no idea of it.

Please help me out to make a bucket of time period

to know that how many open cases are open in 1 to 3 days , in 4 to 7 days.... so on for each month.

Thanks

[Anil_Babu_Samineni]

Usually, We can create buckets like this. Sunny, This is just guess correct me if i am wrong

Use this as a calculated dimension

If([Open case] >=Day([Open case],'1') <= Day([Open case],'3'), Dual('1 to 3 days',1),

If([Open case] >=Day([Open case],'4') <= Day([Open case],'7'), Dual('4 to 7 days',2),

If([Open case] >=Day([Open case],'8') <= Day([Open case],'16'), Dual('8 to 16 days',3),

If([Open case] >=Day([Open case],'17') < Day([Open case],'30'), Dual('17 to 30 days',4),

If([Open case] >=Day([Open case],'30'), Dual('More than 30 days',5))))))

[sunny_talwar]

This is right Anil, but we need to use an expression instead of Open Cases because Open cases is a calculation here and not a field from the script. We will need to use Aggr() function to get the results.

[sunny_talwar]

Anil -

I take my response back, you might be right after all. I am testing and will let you know soon.

Best,

Sunny

[sunny_talwar]

From the sample provided, what is your expected result?

[tyagishaila]

@Sunny

Happy Dussehra!

Suppose we are checking for 8-oct

Created Date	Close Date	OPen for 8-oct
1-oct-16	1-oct-16
2-oct-16	2-oct-16
3-oct-16		1
4-oct-16		1
5-oct-16	7-oct-16
6-oct-16		1
7-oct-16	7-oct-16
8-oct-16		1

Output must be

open for 1 to 2 days	open for 3 to 6 days	open for more than 7dys
8-0ct to 6-oct	5-oct to 2-oct	before 1-oct	Total
2	2	0	4

In above output table 2nd row is not a part of output , it is just to understand range of date for which open cases are calculating.

Thanks