Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

Please Help.

Thanks,

AM

1 Solution

Accepted Solutions
Not applicable
Author

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]))

View solution in original post

8 Replies
Jason_Michaelides
Luminary Alumni
Luminary Alumni

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.

Not applicable
Author

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

perumal_41
Partner - Specialist II
Partner - Specialist II

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

Not applicable
Author

Hi Perumal,

Thanks for your response.

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

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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]))
Not applicable
Author

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