Customized aggregation in WebI

[Former Member]

Hello all,

I am currently looking for a solution that will help me aggregate rows in a particular way in Webi:

Suppose I have 2 objects: Country [Hierarchy] and City [Attribute of a Characteristic]

Like shown in the pictures attached, when the Country is repeated because there are many cities, we would like to aggregate these lines: add totals and replace cities by the word 'Various'. When there is only 1 city for 1 country, it stays as is.

Knowing that webi cannot loop through rows, how would one go about doing this? Based on my knowledge, it is impossible to replicate this logic in my underlying BEx query.

Thanks so much,

Nikki

[Former Member · Accepted solution]

Nikki,

Write the below formula in the city column

=If Count([City])In([Country])>1 Then "Various" Else [City]

-Sri Harsha

[Former Member · Accepted solution]

Hi Sri Harsha!

Thanks for your quick reponse! Much appreciated. How will the KFs add up in this case?

[Former Member · Accepted solution]

If the aggregation is sum, for the KF's they would roll up and display values for the country.

Try implementing it and let me know if thats working.

Else you would need to write the below formulae in KF's

Sum([Budget])In([Country])

Sum([Planned])In([Country])

[Former Member · Accepted solution]

It worked! Thanks again!!

[Former Member]

Hi Nikki,

Simply convert Budget & Plan objects into measure on universe & then choose 'Sum' as a projection for same.

Thanks,

Swapnil