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!

[paul_hallwood]

Hello Nirmal,

This would seem to work fine if we had date fields available to us.  But I'm just wondering, for some of our reports the information is only held for Month and Year, of which there are approximately 2 years worth of data.

Is there any way you could manipulate your formula so that it caters for this.  i've tried myself but with no success. 😞

[jagan]

Hi,

Can you please attach some sample data and sample file?

Regards,

Jagan.

[#4]

Hi Paul

   Can you please attach your sample application

[paul_hallwood]

Here's a sample of what I'm trying to do (stripped down and with no sensitive data).

As you can see the current month selected seems to work fine.  When I try to retrieve the previous months data (-1 from the month) nothing seems to come through.

Cast your eyes over it and it'll be much appreciated if you can offer any help.

Thanks everyone for their speedy answers!

[paul_hallwood]

Hi Manish,

The weird thing is, getting the current months activty works, but when I try and get the previous month's activity (by adding -1 to the formula) I don't get any data coming through.

I've attached my example file in reply to someone else's question above if you would like to have a look at this.

[jagan · Accepted solution]

Hi,

Try this expression

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

Regards,

Jagan.

[paul_hallwood]

I've just attched it in reply to someone else asking for it.

I hope this helps! 🙂

[paul_hallwood]

Whoo-hoo!  This seems to have done the trick!

Thanks you everyone for you time spent one this.  It's very much appreciated.

Kind regards,

Paul

[MK_QSL]

Jagan, nice solution... but what if user select 2013 January as current month?