topic Re: Set analysis with If statement in QlikView

Set analysis with If statement

anuradhaa — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Set analysis with If statement

varshavig12 — Tue, 18 Oct 2016 04:07:43 GMT

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

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

Re: Set analysis with If statement

trdandamudi — Tue, 18 Oct 2016 04:28:32 GMT

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

Re: Set analysis with If statement

Anonymous — Tue, 18 Oct 2016 05:11:33 GMT

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,

Re: Set analysis with If statement

punitpopli — Tue, 18 Oct 2016 05:31:45 GMT

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!

Re: Set analysis with If statement

Tue, 18 Oct 2016 05:58:15 GMT

It's not open in qlikview application thiru

Re: Set analysis with If statement

Tue, 18 Oct 2016 06:06:34 GMT

Hi Punit,

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

Re: Set analysis with If statement

sasiparupudi1 — Tue, 18 Oct 2016 06:33:05 GMT

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

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

Re: Set analysis with If statement

Tue, 18 Oct 2016 06:36:38 GMT

Super Sasi, it's working well

Re: Set analysis with If statement

avinashelite — Tue, 18 Oct 2016 06:50:38 GMT

Try like this

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

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

Re: Set analysis with If statement

sasiparupudi1 — Tue, 18 Oct 2016 07:46:06 GMT

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

Re: Set analysis with If statement

trdandamudi — Tue, 18 Oct 2016 16:22:23 GMT

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