Problems displaying Previous Month from User's Selection

[#1]

Hi

I am trying to write an expression in the pivot table which will show the total amount of the previous month from a user's selection.  ie user chooses June, I want it to show May.  It must also filter on Type as Actual.  I have tried NUMEROUS expressions - you will see in the definition window as I have left them all in there just commented out.  I have also tried as a Previous Month as a variable.  I read a few articles as well about "as of" tables but couldn't see how to do this for what I need.

I have attached the file for you and any and all help is much appreciated as I've spent a couple of days on this now and I know this should be fairly simple!!

FYI - I think there might be something special about my Month field as on another formula I used I had to define Month format "MMM YY" in the expression.  I think this is probably what's bringing me undone today.

Cheers,

PS - I have since created a new field for Previous Month which is correct however I cannot work out how to override the user's Month selection and replace / filter with Prev Month.

HELP PLEASE!!!

[kaushiknsolanki]

Hi,

    Try this expression for your actual.

    SUM({$ < Month = {'$(=addmonths(max(Month),-1))'},Type = {"Actual"}>} Amount)

    This will give you the data for the last month, say for example if user selects Dec 11 then data shown will be of Nov 11.

    If no month is selected it will show data for the month prior to maximum month.

Regards,

Kaushik Solanki

[#3]

Hi Kaushik

Thanks for your reply.  This did not work - it returned $0s.  Any other suggestions?

[kaushiknsolanki]

Hi,

    Yes i know that, its because you dont have data for the month of nov 11 and for the actual.

    select some different month and check.

Regards,

Kaushik Solanki

[#5]

Hi

No - doesn't work for any month.

Cheers,

[kaushiknsolanki]

HI,

   Have a look at the attched sample file.

Regards,

Kaushik Solanki

[#7]

BRILLIANT!!!!!  So it was a problem with my document settings.  

Thanks so much!!

[kaushiknsolanki]

Hi,

   Glad that my suggestion worked.

Regards,

Kaushik Solanki