Solved: #Multivalue in merged dimensons; Webi

vaibhav_rathore · ‎11-22-2018

Hi,

I have two queries :

Query1 and Query2

fields coming from query1= posting period, clearing period, budat_month, revenue

fields coming from query2= posting period, clearing period, budat_month, revenue

Requirement is i have to get the running sum of revenue from both the queries and sum up the two and display month wise in one report.

Clearing period of query1 has null values for all fields whereas Clearing period of query2 has the data.

Merged Dimesions: Posting period, Clearing Period and Budat month

prompt= user_yr: BUDAT_YR

variable

V1: =if([Posting Period]<=[user_yr] and [Clearing Period]>[user_yr]; runningsum([Query2.revenue]);0)

the result for above formula is 0 because DEV has no data satisfying the above condition.

Report:

Budat Month RunSum_Revenue

01 #multivalue

02 #multivalue

03 #multivalue

04 #multivalue

05 #multivalue

When i checked box "Avoid duplication of values" then it showed multiple 0 values for each month.

Kindly suggest how to rectify it.

Thanks

amitrathi239 · ‎11-22-2018

try this.

=sum(if([Posting Period]<=[user_yr] and [Clearing Period]>[user_yr]; runningsum([Query2.revenue]);0))

if this will not work try this.

=max(if([Posting Period]<=[user_yr] and [Clearing Period]>[user_yr]; runningsum([Query2.revenue]);0))

vaibhav_rathore · ‎11-23-2018

Hi Amit,

Although using Sum() has solved the #multivalue error, but the result is not coming correct, for some values it is getting double.

While using Max() function it is giving 0 for most values.

Please suggest the necessary steps.

I have also attached the correct result.

The desired format of the report is that includes only Month and Running Sum() from starting date of transaction.

Regards

#Multivalue in merged dimensons; Webi