Create a consecutive range of dates based on date prompts

[Former Member]

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.

[Former Member]

Can anybody help me please?

[amitrathi239]

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

=TimeDim([your object];DayPeriod)

[Former Member]

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.

[amitrathi239]

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

[Former Member]

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

[amitrathi239]

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

[Former Member]

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