MonthName() Previous Month

[#1]

Hello All,

I have the following data set

| MonthName | Amount | Type

| March 2011 | 100 | Settled

| Feb 2011 | 200 | Settled

| Jan 2011 | 300 | Settled

I need to work out the set analysis to work out 'Previous Month' expression, so the output is as follows:

| MonthName | Amount | Previous Month

| March 2011 | 100 | 200

| Feb 2011 | 200 | 300

| Jan 2011 | 300 |

Any help would be great!

Thanks

BR

[Miguel_Angel_Baeyens]

Hello,

Assuming it's a pivot table, and MonthName is the dimension, and Amount the label of the sum of amount expression,

Below([Amount])

Should return the value as you expect.

Hope that helps.

[#3]

Hey

Thanks for your reply,

Unfortunately it didnt work! im using set analysis on the 'Amount' Column:

AVG({$<Type = {'Settled'}>}Amount)

I have amended my first post to reflect,

Thanks

BR

[Miguel_Angel_Baeyens]

Hi,

The only thing you need is to label the expression, regardless the complexity of it. Clear all selections and see if that changes. You would need to use

Below(TOTAL [Amount])

To get it working.

Hope that helps.

[#5]

Hey,

Yes - without selections, that is now working!

But when select March '2011' - i still need to bring through the previous month (Feb 2011)

Thanks

BR

[#6]

Bump - anyone?

[Miguel_Angel_Baeyens]

Hello,

You will need to create a new field in your script that stores the previous month, since you don't want just display previous month amounts, but making a selection and show always for each record the amount for the previous month. Search the forums for "AsOf" table.

Hope that helps.