Set Analysis - multiple scenario selections

[newqlik2017]

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

[vishsaggi]

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 · Accepted solution]

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

In your table.

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

[newqlik2017]

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]

Thank you both.