Group by a specific filter before creating island tables in load script

[tas_taba1]

Hello Qlikkers,

I have a stratified cluster sampling  dataset like this-

Now, the dataset has total 3 strata and 10 clusters. But some of the years have some clusters(CinemaPSU)missing. For example, the years- 2015, 2016 and 2017 have two clusters missing. In QS app, I used the fields- CinemaStratum, CinemaPSU and UtilityWt to calculate the measure Standard Error-

Standard Error

The expression for the SE calculation in the KPI is-

=Sqrt(Sum( {1} Aggr( 
((sqr(sum(aggr(sum(DISTINCT [UtilityWt]), [PersonID])) - sum(Total <CinemaStratum> aggr(sum(DISTINCT [UtilityWt]), [PersonID]))/Count({1<Year=P(Year)>} DISTINCT TOTAL <CinemaStratum> CinemaPSU))) * 
Count({1<Year=P(Year)>} DISTINCT TOTAL <CinemaStratum> CinemaPSU)/(Count({1<Year=P(Year)>} DISTINCT TOTAL <CinemaStratum> CinemaPSU)-1)), CinemaStratum, CinemaPSU)))

In the table, the SE is calculated for each Race dimension. But when I choose a specific race value from the filter, it gives me a different result-

SE with Filter

I want to get the same SE for the specific race type in the table as well. The expression used for the SE calculation in the chart is-

=If(RowNo(TOTAL)=0 , 
	Sqrt(Sum( {1} Aggr( 
    ((sqr(sum(aggr(sum(DISTINCT [UtilityWt]), [PersonID])) - sum(Total <CinemaStratum> aggr(sum(DISTINCT [UtilityWt]), [PersonID]))/Count({1<Year=P(Year)>} DISTINCT TOTAL <CinemaStratum> CinemaPSU))) * 
    (Count({1<Year=P(Year)>} DISTINCT TOTAL <CinemaStratum> CinemaPSU)/(Count({1<Year=P(Year)>} DISTINCT TOTAL <CinemaStratum> CinemaPSU)-1))), CinemaStratum, CinemaPSU))),
    Sqrt(Sum( {1} Aggr( 
    ((sqr(sum(aggr(sum(DISTINCT [UtilityWt]), [PersonID], Race)) - sum(Total <CinemaStratum,Race> aggr(sum(DISTINCT [UtilityWt]), [PersonID], Race))/Count({1<Year=P(Year)>} DISTINCT TOTAL <CinemaStratum> CinemaPSU))) * 
    (Count({1<Year=P(Year)>} DISTINCT TOTAL <CinemaStratum> CinemaPSU)/(Count({1<Year=P(Year)>} DISTINCT TOTAL <CinemaStratum> CinemaPSU)-1))) ,Race, CinemaStratum, CinemaPSU))))

My data model is  designed in such a way that no matter what filter values are chosen it will aggregate all the strata and clusters present in the dataset and make the corresponding utilitywt-0 in the SE calculation. The code used to create these island tables are given below-

Data:
LOAD
    TheatreID,
    PersonID,
    AgeYrs,
    CinemaStratum,
    UtilityWt,
    CinemaPSU,
    Year,
    Race,
    Race&CinemaPSU&CinemaStratum as Key1,
    Gender
FROM [lib://main/Dummy_dataset.xlsx]
(ooxml, embedded labels, table is Dataset);

All_cinemaPSU1:
Load FieldValue('Race',RecNo()) as Race
AutoGenerate FieldValueCount('Race');
Join(All_cinemaPSU1)
Load Distinct CinemaPSU,
              CinemaStratum
Resident Data;
All_cinemaPSU:
Load  *,
     Race&CinemaPSU&CinemaStratum as Key2
Resident All_cinemaPSU1;
Drop Table All_cinemaPSU1;
Concatenate(Data)
Load *,
     0 as UtilityWt
Resident All_cinemaPSU
where not Exists(Key1,Key2);
Drop Table All_cinemaPSU;
Drop Fields Key1,Key2;

Since I chose the years 2015,2016 and 2017 from the year filter, the total no. of clusters/PSUs present in the dataset are now 8 (not 10). But it is still aggregating all of the clusters (10) in the load script.

I want to know if there is a way to group by the field- Year first and then aggregate all the strata/PSU's present in the dataset to create the keys in the load script.  Basically I want all the stratums/PSU's  present in the selected years to be included and their corresponding utilitywt values to be considered as '0'.

Any help regarding this will be highly appreciated. @sunny_talwar and @Kushal_Chawda  you guys helped a lot with my previous inquiries regarding this application. Hope you have some time to take a look at this too. I am also attaching the app here for reference. 

Thank you.