Set analysis with If statement

[anuradhaa]

I have below data set

Country	City	Sales
USA	NweYork	2000
USA		520
UK	London	200
UK		150
AUS	Sydney	8000
AUS	perth	560
USA	Chicago	580

I want to get

Country	City Sales	empty City Sales
USA	2580	520
UK	200	150
AUS	8560	-

From Above data set. I use Country as dimension. and use separate set expressions for others. but i don't know how to get both the fields with and without country in single raw in pivot table

Thanks

[sasiparupudi1]

If you are using city also as a dimension then you could use it as a calculated one as follows

=if(len(Trim(City))>0,City,'No City')

then the single expression sum(Sales)  will show all values in single column.

dimensions Countrry,calculated dimension =if(len(Trim(City))>0,City,'No City')

expression  Sum(sales)

hth

Sasi

[trdandamudi]

Might be version difference ? The app I created was in version 11.2