Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
Customer Spotlight: Discover what’s possible with embedded analytics Oct. 16 at 10:00 AM ET: REGISTER NOW
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Not applicable
‎2013-05-17 08:09 AM

Simple (?) Count

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

2013-05-17 14-01-05.jpg

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

1,069 Views
0 Likes
12 Replies
Not applicable
‎2013-05-21 07:44 AM
Author

In response to swuehl

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

170 Views
0 Likes
swuehl
MVP
MVP
‎2013-05-21 09:08 AM

In response to

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.

NotificationUser statusCompletn dateNotif.date
10766767CLSD15.01.201308.01.2013
10770184CLSD15.01.201314.01.2013
10770186CLSD15.01.201314.01.2013
10770406CLSD21.01.201315.01.2013
10773467CLSD21.01.201321.01.2013
10770017CLSD22.01.201314.01.2013
10774250CLSD22.01.201322.01.2013
10775196CLSD23.01.201323.01.2013
10776631CLSD29.01.201325.01.2013
10778208CLSD30.01.201328.01.2013
10774219CLSD31.01.201322.01.2013
10774216CLSD31.01.201322.01.2013
10778654CLSD31.01.201329.01.2013
10778343CLSD05.02.201328.01.2013
10766179CLSD08.02.201307.01.2013
10778204CLSD08.02.201328.01.2013
10779286CLSD08.02.201330.01.2013
10778340CLSD20.04.201328.01.2013
10766178IPRG
07.01.2013
10771399IPRG
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.

NotificationUser statusCompletn dateNotif.date
10782636CLSD04.02.201304.02.2013
10782638CLSD04.02.201304.02.2013
10782624CLSD04.02.201304.02.2013
10782622CLSD04.02.201304.02.2013
10782857CLSD08.02.201305.02.2013
10786835CLSD11.02.201311.02.2013
10786837CLSD11.02.201311.02.2013
10784637CLSD18.02.201307.02.2013
10791902CLSD19.02.201319.02.2013
10792143CLSD19.02.201319.02.2013
10792111CLSD19.02.201319.02.2013
10792142CLSD19.02.201319.02.2013
10792826CLSD19.02.201319.02.2013
10786963CLSD20.02.201312.02.2013
10791282CLSD20.02.201318.02.2013
10792106CLSD20.02.201319.02.2013
10792102CLSD21.02.201319.02.2013
10796210CLSD26.02.201325.02.2013
10792145CLSD13.03.201319.02.2013
10786804CLSD19.03.201311.02.2013
10792110CLSD24.04.201319.02.2013
10791269CLSD25.04.201318.02.2013
10791884CLSD25.04.201319.02.2013
10793616IPRG
20.02.2013
10793621IPRG
20.02.2013
10794052IPRG
21.02.2013
10794352IPRG
21.02.2013
10794844IPRG
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.

test2-82389b.qvw
791 Views
0 Likes
Not applicable
‎2013-05-22 04:26 AM
Author

In response to swuehl

Hi

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

Best regards

/Mats

170 Views
0 Likes