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

[swuehl]

I believe your request is similar to this one: http://community.qlik.com/message/342059#342059

so please have a look at the proposed solution over there.

Please also recheck your expression:

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

I believe you want to compare the string returned from MID, not 4 with a string?

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

Then, to prevent to count matching as well as not matching [User status] values, you should use something like

if(Mid([User status],1,4) = 'CLSD', [User status])

with an implicite NULL for not matching entries.

--> count( if(Mid([User status],1,4) = 'CLSD', [User status]) )

[#3]

Hi

Thanks for your answer but it's not really what i had in mind.

Please look at the attached files.

Dimension is Compl.date (jan-april)

On one bar i would like to have the number of closed (CLSD) notifications (15, 23, 71, 38) and on an other bar i would like to have the total (CLSD+IPRG) of notifications for that specific month (41, 49, 97, 64).

My final composition should be the difference between number of closed notifications and the total number of notifications in percent (Notification Gap for each month), based on Compl.date.

Best regards

/Mats

[swuehl]

Sorry, I don't get how you calculate the total number for a month, for example 41 for jan, taken your sample xlsx.

[#5]

Hi

If I filter the Completn date with January+blanks I get all notifications for that month, CLSD and IPRG, 41 in total.

/Mats

[swuehl]

I only get 41 records for Jan if I count incl.  Notifications like 10793616, which shows a Notif. date 20.02.2013, which is past Jan 2013. So you want to count all Notifications, which are not closed, but may show up in the future?

[#7]

Hi

You are right…

It should of course be the notifications with notif.date in each month that are interesting.

In this case for January its 17 created and 15 closed notifications. For February its 23 created and 18 closed.

It’s that gap in percent I am interested in.

Best regards

/Mats

[swuehl]

Still don't get how you calculate your numbers, I calculate

23 closed (status CLSD) and 28 total (23 + 5 with status IPRG) for month February.

[#9]

Hi

Look at the pic. 23 in total for february. 18 CLSD, 5 IPRG

Notif.date and Completn date are both filtered for february.

/Mats

[swuehl]

I am getting more and more confused.

So you don't want to count the 5 issues as CLOSED for month Feb 2013, that were created in December 2012 and Jan 2013?

Please compare with your number for CLOSED issues in your second post, where you required a number of 23 (now you are telling 18).

I don't have much more time too spend on this, sorry.