De-Normalizing a dimension for multiple fields in a straight table

[tas_taba1]

Hello Qlikkers,

I have a dataset like this-dataset

Now, I want to create a straight table in Qlik Sense that will have Race, CinemaStratum, CinemaPSU as dimensions and sum(UtilityWt) as measure. I want the straight table to look like this-

expected

But I want all the CinemaStratum and CinemaPSU to be included for each of the races. Even if some of the cinemaStratum/cinemaPSU does not have utilityWt, I want 0  to be inserted in that row for the measure i.e. I want to have 10 rows(total no. of PSUs) for each of the races. All I have is the table below which does not include the missing stratums/PSUs.

current 

I am at my wits' end about how to proceed further. I have created an island table but with my little experience of data modeling I can't really connect it to the fact table and compare to include all the values. I don't even know if that's a feasible solution. I would appreciate any help or suggestions.I am really looking forward to hearing from all the experts here! I am attaching my dataset and application(please refer to sheet2) here for reference.

Thank you.

[Lisa_P]

The problem is that you are not bringing in the data that has those dimensions. In your Dataset table, the combination of African American, Small Cinema Hall only has values of 5,6,7 for CinemaPSU.

So it is the conversion from the original table to the Dataset that is the problem.

[Kushal_Chawda · Accepted solution]

@tas_taba1  see the attached

[tas_taba1]

Hi @Kushal_Chawda ,

You are a savior! I now got an idea of how joining works and its really insightful.

However, I have a followup question which in essence should be easy but alas I can't figure it out. If I were to now calculate the sum of the measure- interim (shown in picture below) for each race type how would I do that? I thought using Aggr() over race would solve it but apparently not.

desired

I would later have some calculations based on this, i.e. square root of each of these sums. Attached is the updated qvf file.

Edit:

Hi @Vegar,  I saw your blog post on how to create "Subtotal in Straight Table". I am not really sure if that is what I need in this case. Could you please lend your expert opinion and help me out? Thank you.

[sunny_talwar · Accepted solution]

@tas_taba1 try this

=Sum(TOTAL <Race> Aggr( 
((sqr(Sum(UtilityWt) - Sum(Total <CinemaStratum,Race> UtilityWt)/Count(DISTINCT ALL <CinemaStratum> CinemaPSU))) * (Count(DISTINCT ALL <CinemaStratum> CinemaPSU)/(Count(DISTINCT ALL <CinemaStratum> CinemaPSU)-1)))
, Race, CinemaStratum, CinemaPSU))

[tas_taba1]

Hi Sunny,

Thanks a lot for the response. I just figured it out literally 10 seconds ago as well! 😄

If I have only one dimension Race in the table:

'=Sum( Aggr(

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

, CinemaStratum, CinemaPSU, Race))'

Again, thank you!

[tas_taba1]

Hello Qlikkers,

I am facing another challenge in my application and would highly appreciate any expert help/suggestions.

So, previously I have aggregated some of the analysis variables in my dataset(e.g. race, gender) with the fields CinemaStratum and CinemaPSU to create aggregated key in the load script. After that, I was able to use them as dimensions in different straight tables and get my desired calculated measure- SE for each row. But now when I try to create pivot tables with both the variables as rows, it does not give me the same row by row value as in the straight tables.

example-1example-2

For example, when I chose female from the filter pane the row by row SE calculations in the straight table (NE Calculations Race) does not match with the corresponding SE calculations in the pivot table. 

@Kushal_Chawda  and @sunny_talwar ,

Since you both are familiar with my data model and helped me unwind some of the previous complications in my application, I am looking forward to receiving feedback from you guys on this. I greatly appreciate your help.

Attached is the latest version of my app(please refer to sheet-3). Thank you!

[sunny_talwar]

@tas_taba1 I guess the question here is that which of the two is giving you the correct answer? or which of the two are you looking to see in both the tables?

[tas_taba1]

Hi @sunny_talwar , the SE (Standard Error) calculations in the straight tables(with only one dimension) are correct because I validated them in SAS-SAS output

I also did the row by row calculations in excel. I am attaching the excel file for your reference. 🙂

So, I am looking to get the same SE results as straight table for each row in the pivot table (similar to SAS output).

[sunny_talwar]

@tas_taba1 Not sure, but may be try this

=if(Dimensionality()=1,
(Sqrt(Sum( {1} Aggr( 
((sqr(Sum(UtilityWt) - Sum(Total <CinemaStratum,Gender> UtilityWt)/Count(DISTINCT ALL <CinemaStratum> CinemaPSU))) * (Count(DISTINCT ALL <CinemaStratum> CinemaPSU)/(Count(DISTINCT ALL <CinemaStratum> CinemaPSU)-1)))
, Gender, CinemaStratum, CinemaPSU)))),
Sqrt(Sum(total <Race> {1} Aggr( 
((sqr(Sum({1}Aggr(Sum(UtilityWt) - Sum(Total <CinemaStratum,Race> UtilityWt)/Count(DISTINCT ALL <CinemaStratum> CinemaPSU), Race, CinemaStratum, CinemaPSU)))) *
Sum({1}Aggr((Count(DISTINCT {1} TOTAL <CinemaStratum> CinemaPSU)/(Count(DISTINCT ALL <CinemaStratum> CinemaPSU)-1)), Race, CinemaStratum, CinemaPSU)))
, Gender, Race, CinemaStratum, CinemaPSU))) * Avg(1)
)