Resolving Multi-Value Error for Dates with Multiple Dimension Values in Calculation

[former_member672670]

Hi All,

I am trying to create a Webi report with two columns - "Date" and "Total Accounts". The dates need to be continuous for one month (i.e. Jan 1, 2, 3, 4,.....31) which is why I created a variable using the TimeDim() function for my datetime object as follows.

V_Start Date = TimeDim([Start Date])

Also, the date range needs to be between the previous month's beginning date and the previous month's end date i.e. August 1 - August 31. My query filter prompt has these two dates as parameters ('Begin Date' and 'End Date'), so I have filtered my report block using the variable shown below.

V_Reporting_Period = If([V_Start Date] Between(UserResponse("Enter Begin Date") And UserResponse("Enter End Date]"))) Then 1 Else 0

I have created a variable to count the total accounts as follows.

V_Total Accounts = Count([Account_ID];Distinct) ForEach([V_Start Date]))

This is how my report block looks like.

As you can see, I am getting the multi-value error because there are multiple Account_ID values for some of the dates. When I drag the Account_ID dimension object to the report, the multi-value error disappears, but now there are multiple rows for the same date.

I want the report to display only one row for each date i.e. 8/1/20, 8/2/20, 8/3/20,...8/31/20.

Any ideas on how I can resolve the issue? Thanks.

[ayman_salem]

I've made your example and I have no mistake.

here are my variables:

Begin Date: =FormatDate(ToDate("08/01/2020";"MM/dd/yyyy"); "MM/dd/yyyyy")

End Date: =FormatDate(ToDate("08/31/2020";"MM/dd/yyyy"); "MM/dd/yyyyy")

V_Start Date: =FormatDate(TimeDim([Date]); "MM/dd/yyyy")

V_Reporting_Period: =If(FormatDate([Date]; "MM/dd/yyyyy") Between([Begin Date];[End Date])) Then 1 Else 0

V_Total Accounts: =Count([ID];Distinct) ForEach([V_Start Date])

...

hopefully that helps

[former_member672670]

Thanks for sharing the example. I do have unique dates and the right counts in my report now. However, the filter for V_Reporting_Period does not seem to work in the report block. I am getting all the dates from 2019 and 2020 as well.

[ayman_salem]

it works for me.

Note the formula of "V_Reporting_Period" in bold. It is [date] NOT [V_start date]

[former_member672670]

Right, I did note that. The filter is still not working for me though. I also tried bringing the Date object in a separate query, merging it with the Date object from the original query, and then using the merged dimension. However, the filter is still not working. All the date objects in my report are DateTime objects, so I'm wondering if that has something to do with it.

[ayman_salem]

Time has no effect.

Can you share the formula you used?

[former_member672670]

Here are my variables.

Begin Date = FormatDate(ToDate(UserResponse("Start Date");"MM/dd/yyyy hh:mm:ss:a");"MM/dd/yyyy")

End Date = FormatDate(ToDate(UserResponse("End Date");"MM/dd/yyyy hh:mm:ss:a");"MM/dd/yyyy")

V_Start Date = FormatDate(TimeDim([Date]);"MM/dd/yyyy")

V_Reporting_Period = If(FormatDate([Date]; "MM/dd/yyyyy") Between([Begin Date];[End Date])) Then 1 Else 0

V_Total Accounts = Count([ID];Distinct) ForEach([V_Start Date])

The 'Date' dimension in my query is a Date Time object. Can you not apply filter on a variable using the TimeDim function?

[ayman_salem]

The problem is with the Begin and End dates.

Does the user enter the Begin and End dates with hh:mm:ss AM/PM? the ToDate(UserResponse("Start Date") should be exactly as user input in the prompt.

Add the V_Reporting_Period to the table, and check the values 1 and 0

Also, check if the Begin date and End date are ok or not (put them in the report and refresh)

[former_member672670]

Yes, the user enters the Begin and End dates in hh:mm:ss AM/PM format. I also checked the 0 and 1 values for V_Reporting_Period by dragging it into the report. It is displaying 1 for dates belonging to the year 2019 even though the Begin and End Dates are from '08/01/2020' - '08/31/2020'.

I also dragged the Begin and End dates to the report. They show up as '08/01/2020' and '08/31/2020' respectively.

I did notice that the 'Date' dimension object that is used in the TimeDim formula for V_Start Date returns a multi-value error for some of the rows when I create a new variable: V_Date = TimeDim([Date]) and use it by itself in the report. I thought it might have to do with the fact that 'Date' is a Date Time object, so I changed the data type to date. However, it still displays a multi-value error for some of the rows. I get the same behavior for V_Start Date as well. Do you think that might have something to do with the issue?

[ayman_salem]

I don't really understand the purpose of the user entering the start and end dates with times (especially with :ss) !!!.

Is this a free entry or select from LOV. If from LOV then you don't need to use ToDate

Can you format the text in a cell that contains the variable "Begin and End Date" to see what time it is displayed (format is date and time)?

Without knowing the data you have and the contents of the table, I cannot figure out what the problem of the multi-value error by the object [date] is.

[former_member672670]

The start and end dates are parameters (DataType: Date Time) created in the universe using a formula to get last month's begin and end dates. By default, that's the date prompt that is displayed when refreshing the report, but it is optional and can be changed by the user.

If I format the Begin and End Date in a report cell, I get 08/01/2020 12:00:00:AM and 08/31/2020 12:00:00 AM respectively.

For the multi-value error, what I was trying to say is, If I create a new report with just one object i.e Date and then do TimeDim([Date]), I get a multi-value error for some of the rows. That did not make sense to me, so I though that might have something to do with my issue. Date is a DateTime object in the universe that tells me the date the account started.

[ayman_salem]

Can you also take a screenshot for sample data?

[former_member672670]

I have attached a screenshot of the actual vs the expected output below. As you can see on the right, I only want to see one row for each date.