topic Total amount with if function in pivot table in QlikView

Total amount with if function in pivot table

Tue, 19 Jan 2010 16:07:25 GMT

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

Total amount with if function in pivot table

Tue, 19 Jan 2010 16:36:59 GMT

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.

Total amount with if function in pivot table

Tue, 19 Jan 2010 17:00:08 GMT

Hi Nigel,

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

Andrea

Total amount with if function in pivot table

Tue, 19 Jan 2010 17:11:19 GMT

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.

Total amount with if function in pivot table

Tue, 19 Jan 2010 17:13:30 GMT

Hi,

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

Andrea

Total amount with if function in pivot table

Tue, 19 Jan 2010 17:35:17 GMT

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.

Total amount with if function in pivot table

Tue, 19 Jan 2010 18:19:37 GMT

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