Current Month count

[#1]

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

[Anonymous]

try like:

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

[tresesco]

Create a month field in the script, like

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

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

[Anonymous]

editing syntax:

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

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

distinct Ref)

[jonathandienst]

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?

[tamilarasu]

Hi Emma,

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

[#7]

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

[Anonymous]

Did you try what I suggested you?

[tamilarasu]

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 .....

  .....

[#10]

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 "-"