8 Replies Latest reply: Apr 26, 2012 12:38 PM by Amitesh Modi

# Expression Help

Hi,

I need help with an expression. I have two calculations which return 5 and 4 records respectively. 4 records returned by these 2 expressions are common. I need an intersection expression that results in a total of 5 records. i.e 5 records intersect with 4 records = 5 records (as 4 records are common)

What should be my expresssion?

=count({<Category = {'Software'}, Type = {'SCS CD Quick'}, Item = {'New User/Deactivation'},  Customer = {'Internal'},

Status = {'Assigned','Pending', 'Work In Progress'} >}
Distinct if(((AsOfDate - CreatedDate))>3 and Month(CreatedDate) = 1 AND  Year(CreatedDate) = 2012,[Case ID]))

=count({<Category = {'Software'}, Type = {'SCS CD Quick'}, Item = {'New User/Deactivation'},  Customer = {'Internal'},

Status = {'Closed','Resolved'} >}
Distinct if(((ResolvedDate - CreatedDate))>3 and Month(CreatedDate) = 1 AND  Year(CreatedDate) = 2012,[Case ID]))

The SQL for what I'm trying to do is the following

I'm unable to code for that part of the SQL which is in bold below

SELECT count(DISTINCT [Case ID])

FROM [DB].[dbo].[Table]

where Category ='Software'

AND [Type] = 'SCS CD Quick'

AND Item = 'New User/Deactivation'

AND Customer = 'Internal'

AND( ((DAY([Resolved Date/Time]) - DAY([Create Date]))>3 AND Status in ('Closed', 'Resolved') )

OR  (((DAY([AsOfDate]) - DAY([Create Date]))>3 AND Status in ('Assigned', 'Pending', 'Work In Progress'))) )

AND MONTH([Create Date])  = 1

AND YEAR([Create Date])  = 2012

SQL returns 5 records (or a count of 5).

Thanks,

AM

• ###### Expression Help

Can you post your app?  Date differences are tricky in set anlysis but possible.  Would be easier to help with some data to test on.

• ###### Re: Expression Help

Hi Jason,

I have attached the stripped down version of the file, which includes all the columns necessary for the working of this calculation.

Thanks,

AM

• ###### Re: Expression Help

Hi amit ,

Please check this Expression .i thought useful for u.

count({<Category = {'Software'}, Type = {'SCS CD Quick'}, Item = {'New User/Deactivation'},  Customer = {'Internal'}, Status = {'Assigned','Pending', 'Work In Progress'} >

-<Category = {'Software'}, Type = {'SCS CD Quick'}, Item = {'New User/Deactivation'},  Customer = {'Internal'}, Status = {'Closed','Resolved'} >}

Distinct if((((AsOfDate - CreatedDate))>3 and Month(CreatedDate) = 1 AND  Year(CreatedDate) = 2012) or (((ResolvedDate - CreatedDate))>3 and Month(CreatedDate) = 1 AND  Year(CreatedDate) = 2012),[Case ID]))

Regards

Perumal A

• ###### Re: Expression Help

Hi Perumal,

Your expression works for a few cases (Jan, Feb, Mar) but when I checked the numbers in my query for Oct, Nov and Dec of 2011, the numbers do not match. I do not think SetX- SetY will work here. Basically the two expressions (and I should have mentioned this earlier) are mutually exclusive scenarios, but over a month either first or second or both may occur. In our dataset we have daily snapshots of the ticket status so the first expression (with Status = 'Assigned','Pending', 'Work In Progress') can occur earlier in the month and then the condition for the second expression (with Status ='Closed', 'Resolved') can occur later in the month. Thus, we get two set of tickets, one that satisfies first and the other that satisfies the second condition. From the resultant two sets, I need all the distinct tickets as I do not want to count a ticket twice.

Thanks,

AM

• ###### Re: Expression Help

Why not do a distinct count on an aggregation of the Case Id's returned by Perumal's expression...

=

count(distinct aggr(

count({<Category = {'Software'}, Type = {'SCS CD Quick'}, Item = {'New User/Deactivation'}, Customer = {'Internal'}, Status = {'Assigned','Pending', 'Work In Progress'} >}
Distinct if(((AsOfDate - CreatedDate))>3 and Month(CreatedDate) = 1 AND Year(CreatedDate) = 2012,[Case ID]))
+

count({<Category = {'Software'}, Type = {'SCS CD Quick'}, Item = {'New User/Deactivation'}, Customer = {'Internal'}, Status = {'Closed','Resolved'} >}
Distinct if(((ResolvedDate - CreatedDate))>3 and Month(CreatedDate) = 1 AND Year(CreatedDate) = 2012,[Case ID]))
,
[Case ID]))

/Hans

• ###### Re: Expression Help

Hi Hans,

The expression did not return the correct number of records. Is the above expression correct? I feel that Count of Case ID in the first and second expression will return only counts (i.e a number) so a count distinct of these numbers may/will not return the correct value. Maybe I'm missing something here. Please correct me if I'm wrong.

The question can be asked in this manner; How in the expressions 1 and 2 can I get a list of Case ID's (with all the filter conditions in place) so that when I do the count distinct of those 2 lists, I can get a distinct count of records/tickets/Case IDs?

Thanks,

AM

• ###### Re: Expression Help
Hi,
You are totaly right... Started to write before my thinking process was finished...
What do you think of the following instead?
=
count({<Category = {'Software'}, Type = {'SCS CD Quick'}, Item = {'New User/Deactivation'}, Customer = {'Internal'} >}
Distinct if(Year(CreatedDate) = 2012 and Month(CreatedDate) = 1 and
(
((
AsOfDate - CreatedDate)>3 and (Status='Assigned' or Status='Pending' or Status= 'Work In Progress'))
or
((
ResolvedDate - CreatedDate)>3 and (Status='Closed' or Status='Resolved' ))
),

[Case ID]))
• ###### Re: Expression Help

Hi Hans,

It works, but I'm seeing slight variation in the numbers in the SQL query vs QV. However, the numbers in QV are better as they caught all the cases which were either missed or maybe interpreted differently by the SQL server engine. (these are the boundary condition for the month in question).

So, thanks a lot.

AM