Skip to Content
0

Create a consecutive range of dates based on date prompts

Nov 15, 2016 at 09:50 AM

89

avatar image

Hello,

I am creating a report using Web Intelligence. This report has a date prompt (between).

In the report, I create 2 variables to get Start Date prompt and End Date prompt. I would like to create a consecutive range of date based on Start Date prompt and End Date prompt.

For instance: Run this report with date prompt from 11/07/2016 to 11/11/2016. I would like create a dimension that has values: 11/07/2016, 11/08/2016, 11/09/2016, 11/10/2016 and 11/11/2016.

I find a function TimeDim(param:Dimension), but I do not have a suitable dimension for the parameter of this function.

---More details-----

I have a combined query using Union (query 1 union query 2). Query 1 has a prompt [Closed Date] between "Start" and "End". Query 2 has a prompt [Created Date] between "Start" and "End".

If I use object [Created Date] or [Closed Date] in the function TimeDim, it will give me a consecutive range of date as I wish but the counts of opened tickets or closed tickets maybe incorrect.

As you can see in the image below, if I use object [Created Date] as a parameter for function TimeDim, I will get 1 for Closed on 11/11/2016. It's wrong. I expect It would be 2.

Therefore, I think I cannot pass [Created Date] or [Closed Date] as a parameter to function TimeDim.

I am finding a way to achieve a consecutive range of dates using Start [Prompt] and End [Prompt] only.

Any ideas, please advise.

Thank you so much,

David.

10 |10000 characters needed characters left characters exceeded

Can anybody help me please?

0
* Please Login or Register to Answer, Follow or Comment.

1 Answer

AMIT KUMAR
Nov 15, 2016 at 11:35 AM
0

try with below formula.use same object in the formula which you have used in the prompt

=TimeDim([your object];DayPeriod)

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

Hello Amit,

Let me provide more details.

I have a combine query using Union (query 1 union query 2). Query 1 has a prompt [Closed Date] between "Start" and "End". Query 2 has a prompt [Created Date] between "Start" and "End".

If I used object [Created Date] or [Closed Date] in the function TimeDim, it will give me a consecutive range of date as I wish but the counts of opened tickets or closed tickets maybe incorrect.

As you can see in the image below, if I used object [Created Date] as a parameter for function TimeDim, I will get 1 for Closed on 11/11/2016. It's wrong. I expect It would be 2.

Therefore, I think I cannot pass [Created Date] or [Closed Date] as a parameter to function DateDim.

I am finding a way to achieve a consecutive range of dates using Start [Prompt] and End [Prompt] only.

If you have any ideas or another way to acquire it, please advise me.

Thank you.

0

what is the count formula you have used to calculate closed tickets?

0

I have the following variables:

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

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

[v Is Closed Date In Input Date Range] = If DaysBetween([v Start (Prompt)];[Closed Date]) >= 0 And DaysBetween([Closed Date];[v End (Prompt)]) >= 0 Then 1 Else 0

[v Count Closed] = =Count([Ticket ID] Where ([v Is Closed Date In Input Date Range] = 1))

0

try this.

[v Is Closed Date In Input Date Range] = If DaysBetween([v Start (Prompt)];[Closed Date]) in ([Ticket ID]) >= 0 And DaysBetween([Closed Date];[v End (Prompt)]) in ([Ticket ID]) >= 0 Then 1 Else 0

0

It leads to #MULTIVALUE because there can be many tickets that are closed on the same day.

0