I am using an if statement to show the total of bill amounts per month and at the top of the report there is a total for the year.
The problem I've come across is that if there are 2 amounts for one month. I would usually want to use the 2nd amount.
Using distinct fixes the problem but other accounts will have an incorrect total value because distinct will only take one instance of an amount.
Eg. May 2010 - 15 June 2010 - 15. distinct will only let me take one of those amounts.
The only way around this is if I was to write an if statement to somehow identify if there are 2 of the same months and to take the data in relation to the 2nd instance of the month.
eg. Jan 2011 -20 Jan 2011 -25 my if statement would take the 2nd one.
my code is sum(distinct bill_amount).
if anyone can shed some light on how I would write this if statement or is it possible to use set anaylsis for this type of issue?
Iassen, I've tried to attach a sample but it won't let me.
All I was going to send was a qvw that used a pivot table. The table contained 2 dimensions - Account and Month. The expression was sum(distinct amount) as Total.
This data was loaded in from a spreadsheet.
The table looked like this: Account Month Total Total 2064 12345 Jan 1000 12345 jan 1064
