Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
tas_taba1
Contributor III
Contributor III

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

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

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

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-

output2output2

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_resultwrong_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_resultcorrect_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.

Labels (2)
1 Reply
forte
Partner - Creator
Partner - Creator

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