topic Re: wrong total in pivot table in QlikView

wrong total in pivot table

sadiaasghar — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: wrong total in pivot table

sadiaasghar — Mon, 07 Sep 2020 04:35:46 GMT

@Kushal_Chawda please have a look

Re: wrong total in pivot table

sadiaasghar — Mon, 07 Sep 2020 04:56:46 GMT

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

SUM(

AGGR(

copypasteyourexpressionhere,

Dim1,Dim2,Dim3 // All of your dimensions

)

Re: wrong total in pivot table

Kushal_Chawda — Mon, 07 Sep 2020 09:27:51 GMT

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

Re: wrong total in pivot table

sadiaasghar — Mon, 07 Sep 2020 12:40:04 GMT

@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.

Re: wrong total in pivot table

Kushal_Chawda — Mon, 07 Sep 2020 13:46:33 GMT

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

Re: wrong total in pivot table

sadiaasghar — Mon, 07 Sep 2020 15:07:26 GMT

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

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

Re: wrong total in pivot table

sadiaasghar — Mon, 07 Sep 2020 15:11:03 GMT

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.

Re: wrong total in pivot table

sadiaasghar — Mon, 07 Sep 2020 15:11:35 GMT

@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.

Re: wrong total in pivot table

Kushal_Chawda — Mon, 07 Sep 2020 15:19:50 GMT

@sadiaasghar see the attached

Re: wrong total in pivot table

sadiaasghar — Mon, 07 Sep 2020 19:09:38 GMT

@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?

Re: wrong total in pivot table

Kushal_Chawda — Mon, 07 Sep 2020 20:41:15 GMT

@sadiaasghar see the attached

Re: wrong total in pivot table

sadiaasghar — Tue, 08 Sep 2020 03:54:47 GMT

@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?

Re: wrong total in pivot table

sadiaasghar — Tue, 08 Sep 2020 03:55:44 GMT

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

Re: wrong total in pivot table

sadiaasghar — Tue, 08 Sep 2020 12:50:28 GMT

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

Re: wrong total in pivot table

sunny_talwar — Tue, 08 Sep 2020 12:58:34 GMT

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

Re: wrong total in pivot table

sadiaasghar — Tue, 08 Sep 2020 16:25:07 GMT

@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.

Re: wrong total in pivot table

sunny_talwar — Tue, 08 Sep 2020 18:57:12 GMT

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

Re: wrong total in pivot table

Brett_Bleess — Tue, 22 Sep 2020 16:55:42 GMT

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/NestedAggregations/sum-of-rows-pivot-tables.htm

Regards,
Brett