cancel
Showing results for 
Search instead for 
Did you mean: 

Where of a relative date

Former Member
0 Kudos

Hi gurus,

I need to count the number of events ending tomorrow.

[Tomorrow] =RelativeDate([Day] In Report;1)

[Day] is the prompted day. I have tried:

=[Event] Where([End date] = [Tomorrow]) and got #MULTIVALUE. Count of this, obviously is 0. [Event] and [End date] are dimensions. [Day] is a measure.

Thanks in advance,

Jonás

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hello Jonas,

Could you please share the details where you want to display the count. Meanwhile you can try below.

=Sum([Event] Where([End date] = [Tomorrow]))


Regards

Niraj

Former Member
0 Kudos

I am trying writing the formula in a cell, but I will use in a variable later.

As [Event] is a string dimension, I can do Sum() over it.

Thanks,

Jonás

Former Member
0 Kudos

Ok Since you mentioned Count in the initial post I thought its an number.

But If that is an String and you want that to be shown in a standalone Cell the only way I can think of is to concat the different events

Does that work for you?

Regards

Niraj

Former Member
0 Kudos

Hi Niraj,

I only need to count the number of events. I finally managed it with a measure of the dimension:

[Event measure] =[Event]

And in the formula, I use In() to change the input context:

=[Event measure] Where([End date] = [Tomorrow]) In([Event])

Then I apply the counting:

=Count([Event measure] Where([End date] = [Tomorrow]) In([Event]))

Thanks everyone,

Jonás

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi Jonás,

If [Day] is the prompted day you mean that it's a value enetered by the user.

Is it something you have created using a variable or coming from the Data Provider?

If it's a user prompt you have to create the [Tomorrow] variable such like this: =ToDate(UserResponse(DataProvider([A Variable]); "The Prompt Text"); "yyyyMMdd")

Then you can create your condition: =[Event] Where([End date] = [Tomorrow])

Ensure that the date format is correct.

Didier

Former Member
0 Kudos

Hi,

[Day] has date type. Its value is picked in a calendar by the user in the prompt (query filter).

Thanks,

Jonás

Former Member
0 Kudos

Hi Jonás,

Create a variable "Date prompt" to extract the user prompt: =ToDate(UserResponse("Enter date");"INPUT_DATE_TIME")

Replace "Enter date" by the prompt question.

Then you can create a variable to filter your report: =[Event] Where([End date] = [Date prompt])

I attached a sample WebI  document to illustrate the purpose (rename it to .wid)

Didier