Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

newqlik2017
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!

   

UNITCITYCOUNTYSTATEAMOUNT
EDUCATIONCITY1COUNTY1NJ101
EDUCATIONCITY2COUNTY1NJ102
EDUCATIONCITY3COUNTY2NJ102
EDUCATION-COUNTY1NJ203
EDUCATION-COUNTY2NJ102
EDUCATION -NJ305
EDUCATIONCITY1COUNTY1PA201
EDUCATIONCITY2COUNTY1PA202
EDUCATIONCITY3COUNTY2PA203
EDUCATION-COUNTY1PA403
EDUCATION-COUNTY2PA203
EDUCATION -PA606
EDUCATION---911
1 Solution

Accepted Solutions
vishsaggi
Esteemed Contributor III

Re: Set Analysis - multiple scenario selections

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

In your table.

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

6 Replies
vishsaggi
Esteemed Contributor III

Re: Set Analysis - multiple scenario selections

May be try this?

LOAD *, IF(CITY = '-' OR STATE = '-' OR COUNTY = '-', 0, AMOUNT) AS NewAmt;
LOAD * INLINE
[
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
  ]
;

Your table Dim as UNIT

and Expr use this new Amtlik

= Sum(NewAmt)

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

vishsaggi
Esteemed Contributor III

Re: Set Analysis - multiple scenario selections

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

In your table.

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

newqlik2017
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 ?

Re: Set Analysis - multiple scenario selections

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

newqlik2017
Contributor

Re: Set Analysis - multiple scenario selections

Thank you both.