Pivot Table - Totals Incorrect

[#1]

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.

[Oleg_Troyansky]

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

[sunny_talwar]

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

[maxgro]

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

)

)

[#5]

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.

[#6]

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.