Customized aggregation in WebI
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,
Write the below formula in the city column
=If Count([City])In([Country])>1 Then "Various" Else [City]