Total amount with if function in pivot table

[#1]

Hi,

I have two tables Sales and Forecast connected by Customer ID.

I have also an input field (Competenza_Forecast) that set the reference month. In a pivot table I have the following formula:

if(Competenza_Forecast >= [Mese.Mese],

sum(Forecast.Forecast),

sum(FatNetto)

)

This enable the user to see in the pivot the total amount of sales for each customer until the month selected.

Everithing works fine but the total amount made by QV is wrong. Seems that the total is the Sum(FatNetto) independent of the selected month.

Can someone help?

Thanks in advance!

Andrea

[#2]

Have you tried changing the sum type to Sum of Rows?

This should be available to you in the expressions tab of the pivot table properties.

[#3]

Hi Nigel,

not available on the Pivot table. Always selected as Total Expression.

Andrea

[#4]

Yes, sorry about that, rushing ahead with advice before checking.

I think you might need to make a selection to achieve this, or use set analysis function to sum up values up to that month.

[#5]

Hi,

ok no problem, but... How to use set analysis to achive the goal? Have you any idea?

Andrea

[#6]

Just been trying to think this through again, maybe set analysis is not the answer for you because of the IF statement, can you post an example QVW so I can take a look at your dimensions and expressions, it may be that you could use the AGGR function to achieve it, but I'm not sure.

PS: Is there any reason to choose a pivot over a straight table, a straight table would allow you to choose Sum of Rows.

[#7]

Hi,

ok here the model. Check only the Forecast. If you change Riferimento Forecast you can see that no change are made on the total. I need a pivot table, as you ca see, for giving the "navigation" to the users.

Andrea