Previous Months Data

[paul_hallwood]

Hello All,

I hope someone can help with my issue I'm currently experiencing.

Basically, what I want to happen is, when someone selects a month, the previous 11 months of activity are brought into 11 text boxes (one for each previous month).

I'm curently testing this out with just one month and I'm getting no joy.  This is the formula I am using within the textbox that represents the first previous month:

=Sum({$<[Year]={$(=Only([Year]))}, [Month] = {$(=Only([Month])-1)} >} Activity)

I've searched high and low on this forum but the solutions I have tried have not brought me any joy.  Any help is much appreciated.

Thanks in advance!

[#2]

try this

=Sum({$< Datefield = {$(Vprevmonth)} >} Activity)

Variable:

Vprevmonth=Month(addmonths(max(Datefield),-1))

[MK_QSL]

=SUM({$<Year = {'$(=Year(Today()))'}, Month = {'$(=Month(Today())-1)'}>}Activity)

[Anonymous]

Yes What Nirmal is said is correct now copy paste the expression for Frist text box which show previos month and now for each month text box do   Sum({$< Datefield = {$(Vprevmonth-1)} >} Activity) for second text box and again Sum({$< Datefield = {$(Vprevmonth-2)} >} Activity) and so on so now when you select one year and particular month each text box shows previous month activity

Regards,

anant

[paul_hallwood]

I can't seem to get this formula to work Manish.  Any idea why???

[MK_QSL]

Try This

=SUM({$<Year = {'$(=Year(Today()))'}, Month =  {'$(=Date(AddMonths(Today(),-1),'MMM'))'>}Activity)

[paul_hallwood]

Hmm, it's highlighting the MMM part of the formula as if it's in error.  And when I put that into the textbox it returns "-".

[MK_QSL]

=SUM({<Year = {'$(=Year(Today()))'}, Month = {"$(=Date(AddMonths(Today(),-1),'MMM'))"}>}Activity)

[paul_hallwood]

Still not working unfortunately.  It turns everything inbetween the quote marks a redish-purple colour, as if it's a field.

[MK_QSL]

if your Month is in Number format below should work

=SUM({$<Year = {'$(=Year(Today()))'}, Month = {'$(=Month(Today())-1)'}>}Activity)

if your Month is in Text Format below should work

=SUM({<Year = {'$(=Year(Today()))'}, Month = {"$(=Date(AddMonths(Today(),-1),'MMM'))"}>}Activity)

Considering that Month is 'MMM' format..

If none of above is working, let us have your sample file. There should be some Formatting issues..