topic Re: Set Analysis - multiple scenario selections in QlikView

Set Analysis - multiple scenario selections

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

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

Re: Set Analysis - multiple scenario selections

vishsaggi — Wed, 02 May 2018 18:02:50 GMT

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

sunny_talwar — Wed, 02 May 2018 18:05:14 GMT

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

Re: Set Analysis - multiple scenario selections

vishsaggi — Wed, 02 May 2018 18:08:00 GMT

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

In your table.

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

Re: Set Analysis - multiple scenario selections

newqlik2017 — Wed, 02 May 2018 18:18:54 GMT

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

sunny_talwar — Wed, 02 May 2018 18:23:35 GMT

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

Re: Set Analysis - multiple scenario selections

newqlik2017 — Wed, 02 May 2018 19:35:03 GMT

Thank you both.