topic Re: Current Month count in QlikView

Current Month count

Thu, 10 Dec 2015 11:19:30 GMT

Data

Ref ID Date Type Department

01 554 01/10/2015 09:51:58 Call E

02 554 02/10/2015 10:30:02 Call D

03 333 02/12/2015 11:00:00 email E

10 554 01/12/2015 09:51:58 Call E

11 652 02/12/2015 10:30:02 Call F

12 652 02/12/2015 11:00:00 Call F

I wish to create and expression:

Count of "Ref" for the **current month** where the the type is "call"

My result should be "3"

However, I still want to use my selection box for department.

Can anyone advise what expression to use ?

Thank you

Re: Current Month count

Anonymous — Thu, 10 Dec 2015 11:23:34 GMT

try like:

=count({<Date=, Type={'Call'},Date={'>=$(=date(monthstart(max(Date)),'DD/MM/YYYY'))<=$(=date(max(Date),'DD/MM/YYYY'))>} distinct Ref)

Re: Current Month count

tresB — Thu, 10 Dec 2015 11:24:10 GMT

Create a month field in the script, like

Load Month(Date) as Month, ....

Then expression : = Count({<Type={Call}, Month={'$(=Month(Today()))'}>} Ref)

Re: Current Month count

Anonymous — Thu, 10 Dec 2015 11:24:47 GMT

editing syntax:

=count({<Date=, Type={'Call'},

Date={'>=$(=date(monthstart(max(Date)),'DD/MM/YYYY'))<=$(=date(max(Date),'DD/MM/YYYY'))'}>}

distinct Ref)

Re: Current Month count

jonathandienst — Thu, 10 Dec 2015 11:28:04 GMT

Do you mean that you want your result to change when you make a Department selection? Or to stay the same?

Should the 'current' month mean the last month that matches the selections (eg of you select D, the last month is October), today's month (December) regardless of the selections?

Re: Current Month count

tamilarasu — Thu, 10 Dec 2015 11:41:17 GMT

Hi Emma,

You need a month field for your requirement. Check the attachment for better understanding!

Re: Current Month count

Thu, 10 Dec 2015 13:25:11 GMT

Tamil

Thank you very much for putting the example together, I am

still struggling with the date in the load.

and getting

'Date#' is not a recognized built-in function name.

Any ideas ?

Thanks

Re: Current Month count

Anonymous — Thu, 10 Dec 2015 13:32:43 GMT

Did you try what I suggested you?

Re: Current Month count

tamilarasu — Thu, 10 Dec 2015 13:41:09 GMT

What is your data source? Are you fetching data from sql? If so, you need to use preceding load.

Load *,

Date(Date#(DateField....... ;

Select sql .....

.....

Re: Current Month count

Thu, 10 Dec 2015 13:48:05 GMT

Hi Tamil

This is now the first part of my load

Concerns:

Load *,

Date(Date#(CreatedOn,'DD/MM/YYYY hh:mm:ss'),'MMM') as Month;

SQL

SELECT ConcernID,

ProfessionalServices.dbo.QM_Queries_Concerns.QueryID,

ProfessionalServices.dbo.QM_Queries_Concerns.SalesDoc,

Item,

CreatedOn, .................

doesnt give an error now but my field contains nothing but "-"

Re: Current Month count

tamilarasu — Thu, 10 Dec 2015 13:54:28 GMT

Fine . Are you sure that your createdOn field format is DD/MM/YYYY hh:mm:ss??

If yes, then try below

Date(Date#(Trim(CreatedOn),'DD/MM/YYYY hh:mm:ss'),'MMM') as Month;

Else you need to alter the Date# function to CreatexOn field format. Let me know.

Re: Current Month count

Thu, 10 Dec 2015 14:07:21 GMT

Appreciate your help.

Re: Current Month count

Thu, 10 Dec 2015 14:08:26 GMT

It worked Balraj - thank you.

However, please can you breakdown the expression with comments to tell me what each

part is doing so I can understand what it is doing ?

Re: Current Month count

tamilarasu — Thu, 10 Dec 2015 14:29:11 GMT

Happy to help you..