Simple (?) Count

[#1]

Hi

I have a bar chart that counts how many notifications that are closed for a specific month.

Count (MID([User status],1,4='CLSD'))

no problems as my dimension is set to look for completion date (month).

Here is the trick: There are still notifications in progress that I also would like to count for the same month but as long as i have completion date as dimension, I can not get this to work.

The ultimate ansver should be a comparison between the closed notifications and the total of notifications for just this month.

I have tried like this:

Date(MonthStart([Completn date]),'MMM YYYY') as Compl.month in the script

Compl.month as Dimension

One expression is Count (MID([User status],1,4='CLSD')) Actual numbers of CLSD is 15

Next expression is Count (Total(Notification)) Actual numbers of notifications is 38

My result is

I would like the red bar to be 38 and eventually i would like to have a percentage between closed and total notifications for a specific month...

Best regards

/Mats

[#2]

Hi

I'm sorry if i take up your time with this.

Yes, thats exactly what i mean.

Based on date of completion (february), how many notifications are created in february (18+5=23) and how many of these are closed (18) or in progress (5).

If I do this based on the Notification date it's easy but then i get a difference in the numbers over time. If someone closes a notification now that are set in february the gap will differ for all months, from february until now.

Im just interested in each month. Based on the completion date.

I hope this will help you some more.

/Mats

[swuehl · Accepted solution]

Ok, based on following table, there are 13 issues closed in Jan that have been created in Jan and 2 issues have been created only (not closed) --> TOTAL 15.

Notification	User status	Completn date	Notif.date
10766767	CLSD	15.01.2013	08.01.2013
10770184	CLSD	15.01.2013	14.01.2013
10770186	CLSD	15.01.2013	14.01.2013
10770406	CLSD	21.01.2013	15.01.2013
10773467	CLSD	21.01.2013	21.01.2013
10770017	CLSD	22.01.2013	14.01.2013
10774250	CLSD	22.01.2013	22.01.2013
10775196	CLSD	23.01.2013	23.01.2013
10776631	CLSD	29.01.2013	25.01.2013
10778208	CLSD	30.01.2013	28.01.2013
10774219	CLSD	31.01.2013	22.01.2013
10774216	CLSD	31.01.2013	22.01.2013
10778654	CLSD	31.01.2013	29.01.2013
10778343	CLSD	05.02.2013	28.01.2013
10766179	CLSD	08.02.2013	07.01.2013
10778204	CLSD	08.02.2013	28.01.2013
10779286	CLSD	08.02.2013	30.01.2013
10778340	CLSD	20.04.2013	28.01.2013
10766178	IPRG		07.01.2013
10771399	IPRG		17.01.2013

Based on this table, there are 18 issues closed in Feb, that have been created in Feb and additional 5 issues have been created but not closed --> Total 23.

You are again losing 5 issues that have been closed in a later month.

Notification	User status	Completn date	Notif.date
10782636	CLSD	04.02.2013	04.02.2013
10782638	CLSD	04.02.2013	04.02.2013
10782624	CLSD	04.02.2013	04.02.2013
10782622	CLSD	04.02.2013	04.02.2013
10782857	CLSD	08.02.2013	05.02.2013
10786835	CLSD	11.02.2013	11.02.2013
10786837	CLSD	11.02.2013	11.02.2013
10784637	CLSD	18.02.2013	07.02.2013
10791902	CLSD	19.02.2013	19.02.2013
10792143	CLSD	19.02.2013	19.02.2013
10792111	CLSD	19.02.2013	19.02.2013
10792142	CLSD	19.02.2013	19.02.2013
10792826	CLSD	19.02.2013	19.02.2013
10786963	CLSD	20.02.2013	12.02.2013
10791282	CLSD	20.02.2013	18.02.2013
10792106	CLSD	20.02.2013	19.02.2013
10792102	CLSD	21.02.2013	19.02.2013
10796210	CLSD	26.02.2013	25.02.2013
10792145	CLSD	13.03.2013	19.02.2013
10786804	CLSD	19.03.2013	11.02.2013
10792110	CLSD	24.04.2013	19.02.2013
10791269	CLSD	25.04.2013	18.02.2013
10791884	CLSD	25.04.2013	19.02.2013
10793616	IPRG		20.02.2013
10793621	IPRG		20.02.2013
10794052	IPRG		21.02.2013
10794352	IPRG		21.02.2013
10794844	IPRG		22.02.2013

Seems to me that this is what you want to see:

Instead grouping by Completion month, you need to group by Notif. month (reason: there is no Completion date for User status IPRG, that you will need to account for in your total issue number for that month).

Then look for the issues that were closed in the same month than created:

=count( distinct {<Notification = {"=[Notif month]=[Completn month]"}, [User status] = {CLSD} >}  Notification)

and count the number of IPRG status for that month:

=count({<[User status] = {IPRG}>} Notification)

adding the two gives you the total (23 for Feb).

I haven't understood how you want to calculate the percentage from the difference, but that should be easy for you.

[#4]

Hi

Thank you so much for this! it really works fine!

Best regards

/Mats