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

[varshavig12]

f - sum({<City={''}>}Sales)

empty - sum({<City-={''}>}Sales)

[trdandamudi · Accepted solution]

You can get your output without If statement. Please take a look at the attached file and hope this helps....

[Anonymous]

Hi Anuradha,

Use following..

Dimension:- Country

Expression1:- City Sales-- Sum({<City={"=Len(City)>0"}>} Sales)

Expression2:- empty City -- Sum({<City={"=Len(City)=0"}>} Sales)

above formula will give you following output.

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

Thanks,

[punitpopli]

You can also use 2 expression like below :

for Empty City sales : =sum({<City={""}>}Sales)

and for City sales : =sum({<City-={""}>}Sales)

Hope this helps!

[#6]

It's not open in qlikview application thiru

[#7]

Hi Punit,

In my qv "-=" won't work. Can you send qvd file.

[sasiparupudi1]

sum({<City={"=Len(City)>0"}>}Sales)

sum({<City={"=Len(City)=0"}>}Sales)

[#9]

Super Sasi, it's working well

[avinashelite]

Try like this

City sales: sum({<City={'*'}>}Sales)

Empty city sales : sum({<City-={'*'}>}Sales) or sum({<City={''}>}Sales)