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]

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.