Auto generated Totals- Auto() and Sum() does not match.

[tas_taba1]

Hello Altruists,

I am new to qlik sense. I am working on one of my projects and I am stuck at this problem for a while now. I have turned to several other related posts in the qlik community but could not resolve the issue. So, any help will be greatly appreciated.

I have a dataset (Capture1.PNG) where only the highlighted columns are considered for creating qs visualization. The second picture (Capture2.jpg) shows the chart I have created in qs app. But the Totals() generated by the table using function sum() (Capture2.jpg) is different from when I use auto() (wrong_result.jpg) for Totals. I am interested in the result generated by the sum() function. And I also need to be able to use this totals result in a KPI. But when I use the same calculations to generate the KPI, it is giving me different result for the sum().

I am really at a loss here. I simply want to generate the sum of individual row values of the field std and use that sum to calculate other measures. I also want to mention the expressions that I am using for the fields in my table:

1. noOfClusters- 'Count(DISTINCT ALL <CinemaStratum> CinemaPSU)'
2. clusterCoeff- 'Count(DISTINCT ALL <CinemaStratum> CinemaPSU)/(Count(DISTINCT ALL <CinemaStratum> CinemaPSU)-1)'
3. clusterBase- 'sum(UtilityWt)'
4. stratumMean- 'Sum(TOTAL <CinemaStratum> UtilityWt)/Count(DISTINCT ALL <CinemaStratum> CinemaPSU)'
5. Interim_std- 'sqr(sum(UtilityWt)-(Sum(TOTAL <CinemaStratum> UtilityWt)/Count(DISTINCT ALL <CinemaStratum> CinemaPSU)))'
6. std- 'sqr(sum(UtilityWt)-(Sum(TOTAL <CinemaStratum> UtilityWt)/Count(DISTINCT ALL <CinemaStratum> CinemaPSU))) * (Count(DISTINCT ALL <CinemaStratum> CinemaPSU)/(Count(DISTINCT ALL <CinemaStratum> CinemaPSU)-1))'

P.S- This is a dummy dataset and does not in any way reflect any data/work from my organization.

[sunny_talwar · Accepted solution]

Try this

Sum(Aggr(

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

, CinemaStratum, CinemaPSU))

[tas_taba1]

Hi Sunny,

Having gone through so many posts, I was hoping that you would notice this post and help out. And you did! Thank you so much. I tried using aggregation function as well but I was using the dimensions in wrong order (CinemaPSU, CinemaStratum). Anyways, thanks again! 

[sunny_talwar]

@tas_taba1  it is strange that the order of the dimension is causing an issue... there are times when order of the dimension does matter, but in this case I don't see them having an impact.

[tas_taba1]

@sunny_talwar  Hi! You are right! My mistake- turns out I was only using one dimension- CinemaPSU. Anyway, this seems to be working for the entire dataset but when I apply filters in the qs app there are discrepancies between the auto generated total and sum generated using aggregation function.

For example- When I filter the data using fields- year and cat/disp the total sum is different than the desired total (std) It is not counting the one clusterBase having 0. I am attaching the screenshot here for better understanding. 

Thank you.

[sunny_talwar]

@tas_taba1 Is this the exact expression you are using or do you have set analysis somewhere in your expression?

[tas_taba1]

Hi @sunny_talwar , it is showing the desired result now I used ALL keyword in the Aggr() function:

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

Anyways, thank you for your prompt reply and taking the time to look!

[sunny_talwar]

Awesome, glad it worked

[tas_taba1]

Hi @sunny_talwar , I am facing a new challenge with my app now and would really appreciate if you could give it a look.

So, If I were to add a 3rd dimension(Race) into the table and put it first in the sorting (shown in problem1.jpg) then how is it possible to still account for all the cinemaStratums and CinemPsus for each of the races even though some of it (races) might not contain all of the stratums/psu's?

I don't know if I am making it clear. But for each of the races now I want to have 10 rows even though some of the corresponding measures might be 0(problem.jpg).

[sunny_talwar]

@tas_taba1 may be it will be better if you can provide a sample where I can see your issue and provide you my inputs by playing around with what you have.