topic Pivot Table - Totals Incorrect in QlikView

Pivot Table - Totals Incorrect

Wed, 02 Sep 2015 15:55:13 GMT

Hello Everyone,

I am facing a problem with incorrect totals showing up on the Pivot table .

The expression that I am using is :

=(sum({<YEAR={$(vMaxYear)},Start_Date={"<=$(vMaxDate)"},Month1=,Type=>}TotalCostofActivity))/(sum({<YEAR={$(vMaxYear)},Start_Date={"<=$(vMaxDate)"},Month1=>}EXCHANGE_RATE))

I am using 3 different dimensions as given below:

ProcessName,

Activityname,

Month1.

I would greatly appreciate if anyone could help me fix this issue.

Re: Pivot Table - Totals Incorrect

Oleg_Troyansky — Wed, 02 Sep 2015 16:00:45 GMT

I don't think that anyone can just guess what's wrong. You'll need to explain your problem in more detail. A sample document would be the best.

One potential issue that I can see from your definition is that your Set Analysis condition is heavily dependent on dates, while Month1 is one of your dimensions. Set Analysis cannot be sensitive to the dimension values, and that can create some unexpected issues.

Other than that - describe your problem and possibly post a sample document that illustrates the problem.

cheers,

Oleg Troyansky

QlikView Your Business: An expert guide to Business Discovery with QlikView and Qlik Sense

Re: Pivot Table - Totals Incorrect

sunny_talwar — Wed, 02 Sep 2015 16:01:09 GMT

What is your expected output? Sum of all the rows? If Yes, then try this:

Sum(Aggr((sum({<YEAR={$(vMaxYear)},Start_Date={"<=$(vMaxDate)"},Month1=,Type=>}TotalCostofActivity))/(sum({<YEAR={$(vMaxYear)},Start_Date={"<=$(vMaxDate)"},Month1=>}EXCHANGE_RATE)), ProcessName, Activityname, Month1))

Re: Pivot Table - Totals Incorrect

maxgro — Wed, 02 Sep 2015 16:02:34 GMT

maybe you have to aggr by your dimensions (if the problem is only on total)

sum(

aggr(

(sum({<YEAR={$(vMaxYear)},Start_Date={"<=$(vMaxDate)"},Month1=,Type=>}TotalCostofActivity))/(sum({<YEAR={$(vMaxYear)},Start_Date={"<=$(vMaxDate)"},Month1=>}EXCHANGE_RATE))

ProcessName,

Activityname,

Month1

)

Re: Pivot Table - Totals Incorrect

Wed, 02 Sep 2015 16:24:15 GMT

Hi Massimo,

Apprecite your suggestion.!

The expression is now giving the totals Correct.

However, the effect of YTD on the expression is not working alright.

For example, if I choose 'July' as the month, the table now shows data only for July month.

But, the requirement is to show YTD data, from Jan to July.

Re: Pivot Table - Totals Incorrect

Wed, 02 Sep 2015 16:27:20 GMT

Hi Sunny,

Your expression worked just fine. The totals are correct now.

However, I am facing issue with displaying data for YTD expression.

For example, if I choose 'July' as the month, the table now shows data only for July month.

But, the requirement is to show YTD data, from Jan to July.