Measure calculation results in Multivalue error or incorrect data

[Former Member]

Hi all,

I'm fairly new to the SAP BO platform so it would be great if I can get some help on this.

I have 3 main objects, say Location, Type of car (amongst Mini, Sedan, Luxury) and Unit Nr (i.e. Unique Id of a specific car)

I have to calculate number of a specific type of car in that location and a measure Rental% [=(Mini+Sedan)/(Mini+Sedan+luxury)]

My table should look something like this :

Location	Car type	Count of car	Rental%
A	Mini	2
A	Luxury	1
B	Mini	5
B	Sedan	7
B	Luxury	8
C	Mini	3
C	Sedan	1
D	Luxury	4

I successfully created three measures for count of car for three types of cars using  If([Car Type]="Mini") Then Count([Unit Nr])  and it works.

The problem is arising with the 4th column Rental%. When I use the previous count measures to create it's formula, all the records display 1.

Actually this measure is tricky because there must be one Rental% for one location, & the setup of the table is prohibiting that.

I tried using cross table as well, but it's not working.

I'd really appreciate any form of help on this. Let me know for ay ambiguity.

Thanks in advance !

[Former Member · Accepted solution]

Hi,

If you want to count the number of cars per location, try the following formula:

=Count([Unit Nr]) ForEach ([Location]) Where ([Car Type] = "Mini"))

Didier

[Former Member · Accepted solution]

Then wouldn't the report need three separate columns to display counts of each car type ?

Moreover, can this help me out to calculate the 4th column ? i have tried using ForEach function in that, but without luck !

[Former Member · Accepted solution]

You neeed to create 3 variables, one for each car.

But in the report it's not mandatory to have one column per car.

You can use this formula to display the appropriate number of cars associated to the car type:

=If ([Car type] = "Mini") Then  [Nb Mini] Else If ([Car type] = "Luxury") Then  [Nb Luxury] Else If ([Car type] = "Sedan") Then  [Nb Sedan]

You can download a WebI document I created based on your data: to be renamed into "Cars.wid"

Didier

[Former Member · Accepted solution]

Hi Didier,

It worked... Thank u so much !

However, the structure of the report is such that it must repeat the Rental % for each location. Is there any way to change the look of the report to something like:

	Mini	Sedan	Luxury	Rental %
A	3	0	0	...
B	2	4	5	...
C	1	6	8	...
D	2	0	9	...

I'm thinking crosstab, but the column Rental% repeats thrice if i attempt so.

One more question, is there any way to display the records having 0 Rental % ? Seems as though they are displaying null values.

[Former Member · Accepted solution]

Hi,

You can modify the formula for each measure that counts the number of car types:

=If IsNull(Count([Unit Nr]) ForEach ([Location]) Where ([Car type] = "Mini")) Then 0 Else Count([Unit Nr]) ForEach ([Location]) Where ([Car type] = "Mini")

See WebI document attached.

Didier

[Former Member · Accepted solution]

Hi,

1) For displaying rental% at the end,

create a cross tab with location as rows, cartype as column and Nb cars as measure.

Then right click the nb cars and insert summary option (this is just to add a column at the end of the cross tab)

then rental percent in this column

2) for replacing nulls

you can use the formula if (isnull(Rental%)=1;0;Rental%)

[Former Member · Accepted solution]

This message was moderated.

[Former Member · Accepted solution]

Hi Divya,

Can't locate any Insert summary option ... can you send a screenshot or anything alike ?

[Former Member · Accepted solution]

[Former Member · Accepted solution]

Hi Divya,

I see, you mean the insert sum option. I applied this technique & it worked perfectly.

Thanks a lot !