QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
cancel
Showing results for
Did you mean:
Creator II

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
Champion III

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

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

6 Replies
Champion III

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

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)))

Champion III

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

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

Creator II
Author

Thanks Sunny!

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

MVP

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

Creator II
Author

Thank you both.