Question Re: WEBI aggregation function containing IN and WHERE clause in Technology Q&A

WEBI aggregation function containing IN and WHERE clause

Former Member — Wed, 03 Jun 2015 10:04:13 GMT

Hello everyone,

I have tried to build a formular that calculates the expected incoming payments for one specific day in a single cell.

The formular works properly if the variable exists in a table with the two for the calculation needed dimensions(dim_expected_income_payment;customer). Although I have inserted theese two contexts in the IN clause of the formular, the correct result is not displayed in a single cell containing the variable. The formular without the WHERE clause is working properly in a single cell. This Displays the total amount of the expected income payments.

My formular is the following:

=Sum([value] In([dim_expected_income_payment];[customer]) Where(DaysBetween([dim_expected_income_payment];[specific_date])=0)) In([dim_expected_income_payment])

I really dont know why this is not working fine. Does anyone has an answer for that topic?

We are using SAP BI SP05 Patch 4.

Any help would be appreciated!

Kind regards,

Christopher

Re: WEBI aggregation function containing IN and WHERE clause

Former Member — Wed, 03 Jun 2015 10:19:45 GMT

Change your first In to a ForEach.

Re: WEBI aggregation function containing IN and WHERE clause

Former Member — Wed, 03 Jun 2015 10:58:09 GMT

Hi Mark!

Thanks for your quick reply!

I have tried your suggestion but this does not work either and Displays only a #notavailable error.

In and ForEach should be same or am I wrong? I thought that the In Operator builds a new context and the ForEach Operator extends an existing context?

Kind regards,

Christopher

Re: WEBI aggregation function containing IN and WHERE clause

Former Member — Wed, 03 Jun 2015 11:30:41 GMT

Yes, In ignores the objects in the block, whereas ForEach adds to those in the block.

I should have asked, what objects are present in your block?

Re: WEBI aggregation function containing IN and WHERE clause

Former Member — Wed, 03 Jun 2015 12:22:34 GMT

Okay, the specific problem is that the formular does not work in an single cell of a cross table. So there is no block correct? Because of this single cell I have used the In operator.

In a table with customer, dim_expected_income_payment and the variable with the formular does it work, but I want to make it work in a single cell of a cross table.

Re: WEBI aggregation function containing IN and WHERE clause

Former Member — Wed, 03 Jun 2015 12:33:35 GMT

just try to create one variable first

var_diff_days = DaysBetween([dim_expected_income_payment];[specific_date])

Now create your final formula like

=Sum([value] In([dim_expected_income_payment];[customer])
Where(var_diff_days in ([dim_expected_income_payment];[customer]) = 0)
In([dim_expected_income_payment])

Now here you are providing output context by using In([dim_expected_income_payment])
so you have to drag the [dim_expected_income_payment] object first and then you can apply the following formula

But if you want final outpul in single blank cell then you have to apply following formula in black cell
==Sum([value] In([dim_expected_income_payment];[customer])
Where(var_diff_days in ([dim_expected_income_payment];[customer]) = 0)

Thanks,

Re: WEBI aggregation function containing IN and WHERE clause

Former Member — Wed, 03 Jun 2015 13:37:08 GMT

Hi swapnil,

thank you for your quick reply. I have tried your proposal but it does not work either with the extra variable. Do you have any further ideas?

Re: WEBI aggregation function containing IN and WHERE clause

Former Member — Wed, 03 Jun 2015 13:43:24 GMT

Can you please show me the snapshot or error or warning you are getting?

That will help to understand the problem

Re: WEBI aggregation function containing IN and WHERE clause

Former Member — Wed, 03 Jun 2015 14:07:52 GMT

Here is a screenshot for you:

I have edited my Formular for the blank cell (expected sum for 02.06.15) in the way you told me.

=Sum([value] In([dim_expected_income_payment];[customer]) Where([var_date_diff] In([dim_expected_income_payment];[customer])=0)) In([dim_expected_income_payment])

Please take a look at the "expected sum for 02.06.15" column in the table below the blank cell. The Formular of the column is:

=Sum([value] In([dim_expected_income_payment];[customer]) Where((DaysBetween([dim_expected_income_payment];[selected_date]))=0)) In([dim_expected_income_payment])

I dont know why this is working in the table, but not in a blank cell.

Re: WEBI aggregation function containing IN and WHERE clause

Former Member — Wed, 03 Jun 2015 14:15:27 GMT

you can try simply following formula

var_diff_days = DaysBetween([dim_expected_income_payment];[specific_date])

then use the following formula:

=Sum([value]
Where(var_diff_days = 0))

or try also
=Sum([value] )
Where(var_diff_days = 0)

Re: WEBI aggregation function containing IN and WHERE clause

Former Member — Wed, 03 Jun 2015 14:22:30 GMT

Hi swapnil,

your two proposals does not work. It displays online an empty cell when I insert the formular in the blank cell.

Edit: Is it possible to add a In Operator to the DaysBetween function?

Re: WEBI aggregation function containing IN and WHERE clause

Former Member — Wed, 03 Jun 2015 14:45:11 GMT

Can you please first check the result for variable var_diff_days = DaysBetween([dim_expected_income_payment];[specific_date]).

Just drag this variable in your table and please attanch the snapshot for same.

I want to check what is the result of newly created variable.

Re: WEBI aggregation function containing IN and WHERE clause

Former Member — Wed, 03 Jun 2015 14:51:31 GMT

here is the screenshot.It looks like the var_diff_days is correct.

Re: WEBI aggregation function containing IN and WHERE clause

Former Member — Wed, 03 Jun 2015 15:11:23 GMT

try following solutions:

=sum(if ([var_diff_days] foreach([dim_expected_income_payment_date])) = 0 then [value] else 0)

=sum([value] where ([var_diff_days] foreach([dim_expected_income_payment_date]) = 0))

Re: WEBI aggregation function containing IN and WHERE clause

Former Member — Wed, 03 Jun 2015 15:25:43 GMT

I have tried both solutions and the result is a multivalue error.

I think we have to add the context to the where clause or am I wrong?

Something like:

=Sum([value] In([dim_expected_income_payment_date];[customer]) Where(DaysBetween([dim_expected_income_payment_date];[selected_date])=0) In([dim_expected_income_payment_date])) In([dim_expected_income_payment_date])

Re: WEBI aggregation function containing IN and WHERE clause

former_member207052 — Wed, 03 Jun 2015 18:08:02 GMT

try the following,

create a dimension (say, ExpectedDateLocal)

=ToDate("11-06-2015";"MM-dd-yyyy")

create a measure (say, AmountOnDate)

=Sum(If DaysBetween([Expected Date];[ExpectedDateLocal])=0 Then [Amountpayable] Else 0)

you can change the expected date as required and you will get the right values as required. attached is the screenshot for your reference.

Re: WEBI aggregation function containing IN and WHERE clause

Former Member — Fri, 05 Jun 2015 13:48:24 GMT

Hi,

thank you for your reply and proposal. I have tried your solution and the formular works well as part of the table, but if the formular is inserted into a blank cell a #MULTIVALUE error is displayed.

I really dont know whats going on here, I would appreciate any further information.

Kind regards,

Christopher Werning

Re: WEBI aggregation function containing IN and WHERE clause

former_member207052 — Fri, 05 Jun 2015 17:09:51 GMT

Once you use any aggregation function (like min,max,sum, average etc) there should not be any "Multivalue" error, simply because no matter the size of your data set the result will always be one number.

Can you confirm if your value field is a measure? If for some reason, you have defined it as dimension (or string) try using ToNumber. Other than this I cannot think of any reason why you are getting the Multivalue error.

Try to prepare a sample data where you/we can replicate the error and post it here to diagnose the issue.