# New to QlikView

Discussion board where members can get started with QlikView.

Contributor

## Set Analysis - multiple scenario selections

Hello,

I'm looking to use set analysis to get the correct value in the below scenario.

1) Select state = NJ, final table should result 305

2) Select state = PA, final table should result 606

3) Select Unit = Education, final table should result 911 (sum of NJ and PA)

4) Select state = NJ, County = county1, final table should result 203

5) Select state = NJ, County = county2, final table should result 102

6) elect state = PA, County = county1, final table should result 403

7) Select state = NJ, County = county2, final table should result  203

QVW file is attached for reference. Thanks!

 UNIT CITY COUNTY STATE AMOUNT EDUCATION CITY1 COUNTY1 NJ 101 EDUCATION CITY2 COUNTY1 NJ 102 EDUCATION CITY3 COUNTY2 NJ 102 EDUCATION - COUNTY1 NJ 203 EDUCATION - COUNTY2 NJ 102 EDUCATION - NJ 305 EDUCATION CITY1 COUNTY1 PA 201 EDUCATION CITY2 COUNTY1 PA 202 EDUCATION CITY3 COUNTY2 PA 203 EDUCATION - COUNTY1 PA 403 EDUCATION - COUNTY2 PA 203 EDUCATION - PA 606 EDUCATION - - - 911
1 Solution

Accepted Solutions
Esteemed Contributor III

## Re: Set Analysis - multiple scenario selections

OR just use this expr without any changes to Load script.

= Sum({< UNIT -= {'-'}, STATE -= {'-'}, COUNTY -= {'-'}, CITY -= {'-'} >}AMOUNT)

6 Replies
Esteemed Contributor III

## Re: Set Analysis - multiple scenario selections

May be try this?

LOAD *, IF(CITY = '-' OR STATE = '-' OR COUNTY = '-', 0, AMOUNT) AS NewAmt;
[
UNIT, CITY, COUNTY, STATE, AMOUNT
EDUCATION, CITY1, COUNTY1, NJ, 101
EDUCATION, CITY2, COUNTY1, NJ, 102
EDUCATION, CITY3, COUNTY2, NJ, 102
EDUCATION, -, COUNTY1, NJ, 203
EDUCATION, -, COUNTY2, NJ, 102
EDUCATION, -, -, NJ, 305
EDUCATION, CITY1, COUNTY1, PA, 201
EDUCATION, CITY2, COUNTY1, PA, 202
EDUCATION, CITY3, COUNTY2, PA, 203
EDUCATION, -, COUNTY1, PA, 403
EDUCATION, -, COUNTY2, PA, 203
EDUCATION, -, -, PA, 606
EDUCATION, -, -, -, 911
]
;

and Expr use this new Amtlik

= Sum(NewAmt)

MVP

## Re: Set Analysis - multiple scenario selections

Try this

=If(GetSelectedCount(COUNTY) > 0,

If(GetSelectedCount(CITY) > 0, Sum(AMOUNT), Sum({<CITY = {'-'}>}AMOUNT)),

If(GetSelectedCount(CITY) > 0, Sum({<COUNTY = {'-'}>}AMOUNT), Sum({<CITY = {'-'}, COUNTY = {'-'}>}AMOUNT)))

Esteemed Contributor III

## Re: Set Analysis - multiple scenario selections

OR just use this expr without any changes to Load script.

= Sum({< UNIT -= {'-'}, STATE -= {'-'}, COUNTY -= {'-'}, CITY -= {'-'} >}AMOUNT)

Contributor

## Re: Set Analysis - multiple scenario selections

Thanks Sunny!

Can we select Unit = Education filter ONLY without selecting any other filters and get the total 911 ?

MVP

## Re: Set Analysis - multiple scenario selections

vishsaggi‌ is doing that... did you check that?

Contributor

Thank you both.