Concatenating values from different tables for a dimension for measure calculation.

[tas_taba1]

Dear Altruists,

I have stumbled upon this problem in my application and was hoping any of you wise people could help me out.

I have this dataset with following fields-dataset

I use the fields CinemaStratum, CinemaPSU and UtilityWt to calculate standard errors-output1

It is giving me the correct SE for the whole dataset. And if I use a filter pane with the required dimension e.g. race it is also showing the correct result for a particular selection like this-

output2

But if I create a separate table with race as the dimension and SE the measure, it is not giving the correct row by row result for each of the race values-

wrong_result

Because now it is not taking into account some of the stratums and PSUs whose value does not exist for a particular race type. But I want all the stratums/psus to be considered and their respective values to be '0' if it does not exist for that field. The expression I am using for SE calculation is-

'sqrt(Sum(ALL Aggr(

Sqr(Sum(UtilityWt)-(Sum(TOTAL <CinemaStratum> UtilityWt)/Count(DISTINCT ALL <CinemaStratum> CinemaPSU))) * (Count(DISTINCT ALL <CinemaStratum> CinemaPSU)/(Count(DISTINCT ALL <CinemaStratum> CinemaPSU)-1))

, CinemaPSU, CinemaStratum)))'

This is the table I am looking for-

correct_result

I want the all the stratums/psu to be included for measure calculations regardless of the selected dimension.

I am attaching my application as well. If anyone can give any insight or share their valuable inputs, it would be great! Thank you.

[forte]

Hi @tas_taba1 

Try adding Race to your aggr function call , something like this

=sqrt(Sum(ALL Aggr(

Sqr(Sum(UtilityWt)-(Sum(TOTAL <CinemaStratum> UtilityWt)/Count(DISTINCT ALL <CinemaStratum> CinemaPSU))) * (Count(DISTINCT ALL <CinemaStratum> CinemaPSU)/(Count(DISTINCT ALL <CinemaStratum> CinemaPSU)-1))

, Race,CinemaPSU, CinemaStratum)))

Hope it helps

Regards