Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
newqlik2017
Creator II
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!

   

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

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

In your table.

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

View solution in original post

6 Replies
vishsaggi
Champion III
Champion III

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)

sunny_talwar

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

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

In your table.

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

View solution in original post

newqlik2017
Creator II
Creator II
Author

Thanks Sunny!

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

sunny_talwar

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

newqlik2017
Creator II
Creator II
Author

Thank you both.