Solved: Re: wrong total in pivot table - Qlik Community

sadiaasghar · ‎2020-09-07

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

sunny_talwar · ‎2020-09-08

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

View solution in original post

sadiaasghar · ‎2020-09-07

@Kushal_Chawda please have a look

sadiaasghar · ‎2020-09-07

I am getting 0 in all rows by wrapping expression using aggr function.

SUM(

AGGR(

copypasteyourexpressionhere,

Dim1,Dim2,Dim3 // All of your dimensions

)

Kushal_Chawda · ‎2020-09-07

@sadiaasghar Can you provide one example? What is wrong and what is expected?

sadiaasghar · ‎2020-09-07

@Kushal_Chawda as you can see in qvw file if one measure like health, safety and environment is selected and month sep 2020 is selected. Total should be sum of all rows of overall score in that particular measure ,' health, safety and environment' currently it's using formula to calculate total also. I don't want my expression formuka to be applicable for total.

Kushal_Chawda · ‎2020-09-07

@sadiaasghar Sorry but still not clear. Can you tell me which number is wrong and what actually number should be?

sadiaasghar · ‎2020-09-07

@Kushal_Chawda Please find attached expected and current output in attachment below

https://drive.google.com/file/d/1d3nOQwLsbanNSz-ghdRvG3kt8pUbd_oT/view?usp=sharing

sadiaasghar · ‎2020-09-07

What i want is that my expression formula should not apply for the total or partial sum. But Total should be equal to sum of all rows.

sadiaasghar · ‎2020-09-07

@Kushal_Chawda What i want is that my expression formula should not apply for the total or partial sum. But Total should be equal to sum of all rows.

Kushal_Chawda · ‎2020-09-07

@sadiaasghar see the attached