WEBI aggregation function containing IN and WHERE clause

[Former Member]

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

[former_member207052]

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.

[Former Member]

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

[former_member207052]

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.

[Former Member]

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,

SB

[Former Member]

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?

[Former Member]

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

That will help to understand the problem

[Former Member]

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.

[Former Member]

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)

[Former Member]

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?

[Former Member]

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.

[Former Member]

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

[Former Member]

try following solutions:

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

or

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

[Former Member]

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])

[Former Member]

Change your first In to a ForEach.

[Former Member]

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

[Former Member]

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?

[Former Member]

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.