Skip to Content
0

Count based on context

Nov 17, 2016 at 05:20 AM

77

avatar image

Hello,

I have a combined query using UNION (query 1 union query 2).

Query 1 has a date prompt using object [created date], which means the date when tickets are created. [Created Date] between "Start" and "End"

Query 2 also has a date prompt using object [Closed Date], which means the date when tickets are closed. [Closed Date] between "Start" and "End"

I have to provide users the report with a layout including 3 columns (dates, open, closed), but the first column (dates) has to list all dates which user input even though there is no ticket created or closed on that date.

For example: Users input date prompt from 11/07/16 to 11/11/16.

The returned data set has created date from 10/09/2016 to 11/11/2016 and closed date from 11/07/2016 to 11/16/2016. Following is what users expect.

I think the first column (dates) has to be a mix between [Created Date] and [Closed Date] because there are dates on which some tickets created and no ticket closed, vice versa.

I created the following variables:

[v Start (prompt)] = ToDate(UserResponse("Start");"INPUT_DATE_TIME")

[v End (prompt)] = ToDate(UserResponse("End");"INPUT_DATE_TIME")

[v Is Created Date in Date Range] = if daysbetween([v Start (prompt)];[Created Date])>=0 and daysbetween([Created Date];[v End (prompt)])>=0 then 1 else 0

[v Is Closed Date in Date Range] = if daysbetween([v Start (prompt)];[Closed Date])>=0 and daysbetween([Closed Date];[v End (prompt)])>=0 then 1 else 0

I created a variable [v Date Dim] for the first column of the report but it has not covered all cases, for instance: assume there are 2 tickets, one opened on 11/10/2016 and closed on 11/11/2016, and the other opened on 11/11/2016, then closed on this day. The result will be 1 for "opened" and 1 for "closed" on 11//11/2016. It's wrong. The correct result should be 2 for closed. (the image below)

[v Date Dim] = if [v Is Created Date in Date Range] = 1 or ([v Is Created Date in Date Range] = 1 and [v Is Closed Date in Date Range] = 1) then [Created date] else [Closed Date]

[v Count Closed Date] = Count([Ticket ID] where ([Project Name] InList("AAA";"BBB") and [v Is Closed Date in Date Range] = 1))

Please advise me on this requirement as well as this solution.

Thank you so much.

David

layout2.png (2.5 kB)
layout.png (3.7 kB)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

ganesh babu Nov 18, 2016 at 06:37 AM
1

hi team,

This is Fiscal Year Variant and Posting Period variant Part.

Step 1: You Confirm Your Company Which One A/C year Follow?

Step 2: Then You change the Period and year shift in OB29.

Step 3: You go Open and close posting period and change the period T. Code - OB52 after doing the business Process.

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Sorry, do you answer in a wrong place?

0
Jyothirmayee A Nov 17, 2016 at 08:41 PM
0

Hi,

What is your [v Is Closed Date in Date Range] formula brings in 1 or 0 for 11/11/2016?. There should be 1 for 11./11/2016 displaying 2 times.

Thanks

Jothi

Show 1 Share
10 |10000 characters needed characters left characters exceeded

yes, "1" is displaying 2 times for 11/11/2016 for [v Is Closed Date in Date Range], but the problem is variable [v Date Dim]. Because the layout of the report contains 3 columns. The first column is Date, this date sometimes is closed date or created date or both.

In the example above, on 11/11/2016, there is not only the ticket created but also closed om that day. Therefore, using the current logic of variable [v Date Dim], the date there means created date, so the count of closed ticket will be 1, not 2.

I am stuck in [v Date Dim].

Please advise me.

Thank you

David

0
Jyothirmayee A Nov 18, 2016 at 05:25 PM
0

Hi,

Then your forumla for Count of closed cases should be

Count(Closed cases) where ([Closed Date] >= [v Date Dim] (which is created date))

Thanks,

Jothi

Share
10 |10000 characters needed characters left characters exceeded