Pivot Table - Conditional Sum

[#1]

Hi,

I am trying to produce a pivot table around dates and to sum data up until the dimension.

I have tried using a sum(if( statement but I cant get this to work.  The application works off two table (costs and accounts).  The costs are updated at regular intervals which formulate the date.

The result I am trying to acheive is below:

Year	31/01/2012	28/02/2012	31/03/2012	30/04/2012
2011	15750	16450	16450	16450

Can anyone help?

Many thanks

[Gysbert_Wassenaar · Accepted solution]

You'll want to add a distinct since for every record in Accounts, you have four in Claims. Try:

Sum(distinct If( Transaction <= LoadDate , Amount, 0))

[#3]

That works; thanks for that.