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.

[tas_taba1]

Hi @sunny_talwar , This is what I have so far (dummy_app.qvf). 

For my std calculations, I need to have all the stratums/clusters, regardless of user selections/filters, included in part of the expression - because I need to perform some calculations on the missing stratums/cluster bases as well. The calculation requires that if the strata/clusters are not in the sample selected by user (via filters), their values need to be set to "0" - therefore, be included in the measure calculations. I need the SE measure for each of the race values as shown in output.jpg.

[tas_taba1]

Hi @sunny_talwar , do you think island table would be an option here?  I have created an island table with two fields- stratum and cluster which has all the values for the CinemaStratum and CinemaPSU. But I am not quite sure how to work it out. I attached the sample app for reference.

Edit: This is the latest post regarding the problem. Please refer to this- https://community.qlik.com/t5/Qlik-Sense-App-Development/Include-all-the-dimension-values-regardless... if you are here.

Thank you!

[tas_taba1]

Hi @sunny_talwar , did you get a chance to look at it?

[cemonteverde]

Hi! I have the same issue. I am new in Qlik and I dont know what is happening. Could you help me? Thanks a lot!

= if (
1-sum( aggr(
fabs(
Sum({<ID={'FORECAST_MENSUAL'}, Estado = {'ACTIVADO'}, [Mes/Año]={'$(vMesAñoActual)'}, [DiferenciaSemanas]={4}, Dev_Kpi={1}, [Semana/Año]= >} value)
-
Sum({<ID={'SALIDAS'}, Estado = {'ACTIVADO'},[Mes/Año]={'$(vMesAñoActual)'}, Dev_Kpi={1} >} value)
)
, [$(vDimension2)] ) )
/ Sum({<ID={'SALIDAS'},Estado = {'ACTIVADO'}, [Mes/Año]={'$(vMesAñoActual)'}, Dev_Kpi={1} >} value)
< 0, 0,
1-sum( aggr(
fabs(
Sum({<ID={'FORECAST_MENSUAL'}, Estado = {'ACTIVADO'},[Mes/Año]={'$(vMesAñoActual)'}, [DiferenciaSemanas]={4}, Dev_Kpi={1}, [Semana/Año]=>} value)
-
Sum({<ID={'SALIDAS'}, Estado = {'ACTIVADO'},[Mes/Año]={'$(vMesAñoActual)'}, Dev_Kpi={1} >} value)
)
, [$(vDimension2)] ) )
/ Sum({<ID={'SALIDAS'}, Estado = {'ACTIVADO'},[Mes/Año]={'$(vMesAñoActual)'}, Dev_Kpi={1} >} value)
)