wrong total in pivot table

[sadiaasghar]

I am getting wrong total for overall score. This expression is working fine but  i am getting wrong total. I am attaching qvw file for reference. What's the issue with this?

if(Variance_Type='has not',(Sum([Overall Step Weightage])*Sum({<KPI={'WOW'}>}[Numeric Field Weightage]))*100,

if(Variance_Type='has',

if((1-[Overall Step Weightage])<=sum({<KPI={'WOW'}>}[Variance]),(Sum({<KPI={'WOW'}>}[Numeric Field Weightage])*1)*100,
if((1-[Overall Step Weightage])<=sum({<KPI={'Focus'}>}[Variance]),(Sum({<KPI={'WOW'}>}[Numeric Field Weightage])*0.75)*100,
if((1-[Overall Step Weightage])<=sum({<KPI={'Par'}>}[Variance]),(Sum({<KPI={'WOW'}>}[Numeric Field Weightage])*0.5)*100,
if((1-[Overall Step Weightage])<=sum({<KPI={'Marginal'}>}[Variance]),(Sum({<KPI={'WOW'}>}[Numeric Field Weightage])*0.25)*100,

Sum({<KPI={'WOW'}>}[Numeric Field Weightage])*0))))))

[sadiaasghar]

@Kushal_Chawda Please have a look at my sheet2 score and yield column. When i apply sum(aggr(expression,dimensions)), it's not working correctly. I guess i am not giving correct dimensions. Please guide me which dimensions i should use?

[Kushal_Chawda]

@sadiaasghar  see the attached

[sadiaasghar]

@Kushal_Chawda as you can see in attached qvw file by applying sum(agggr(expression,dimensions)) its changing all the  rows of score column to zero regardless of what data it actually has. The same was happening with me before. Why it's making all values zero?

[sadiaasghar]

@Kushal_Chawda please your attached qvw file and check it's giving all the score as zero.

[sadiaasghar]

@Kushal_Chawda please help. As your suggestion is not working.

[sunny_talwar · Accepted solution]

Try this

Sum(Aggr(if(Variance_Type='has not',(Sum({<KPI={'WOW'}>}[Numeric Field Weightage])*(Sum(Aggr(Avg({<year_month_num = {$(=Max(year_month_num))}>}[Overall Step Weightage]), Project, Month)))*100),if(Variance_Type='has',if((1-[Overall Step Weightage])<=sum({<KPI={'WOW'}>}[Variance]),(Sum({<KPI={'WOW'}>}[Numeric Field Weightage])*1)*100,
if((1-[Overall Step Weightage])<=sum({<KPI={'Focus'}>}[Variance]),(Sum({<KPI={'WOW'}>}[Numeric Field Weightage])*0.75)*100,
if((1-[Overall Step Weightage])<=sum({<KPI={'Par'}>}[Variance]),(Sum({<KPI={'WOW'}>}[Numeric Field Weightage])*0.5)*100,
if((1-[Overall Step Weightage])<=sum({<KPI={'Marginal'}>}[Variance]),(Sum({<KPI={'WOW'}>}[Numeric Field Weightage])*0.25)*100,Sum({<KPI={'WOW'}>}[Numeric Field Weightage])*0)))))), [Measure/Department], Project))

[sadiaasghar]

@sunny_talwar thanks it's working. Please help me with one more thing i created a mini gauge chart in this pivot table based on yield actual and i want to hide it's total row.

i have tried dimensionality and rowno()=0 condition with my expression but it's not hiding total row chart.

[sunny_talwar]

I don't see in the chart, Is this in a chart on another sheet?

[Brett_Bleess]

Just attaching an additional Help link that I think will likely be helpful to others that run across this thread.

https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/ChartFunctions/...

Regards,
Brett