Summarizing the data for last 13 months based on Selected Month 1

[vega83]

Hello, I have two fields PAID amount and Amount debit
And user has a paramter where he can select the month or date. Suppose if I select todays date I need to get the data from july 08 to july 09. If I select August it should be aug 08 to aug 09 , below is the example. I select todays date. How can I achieve this?. Thanks so much for your time

MOnth Payment Debit
July 20 10
aug 10 66
sept 30
oct 55
nov
dec
jan
feb
mar
apr
may
june
jul

 

[nobull613]

You could try something like:

{table.date_field} in CDate(Month(DateAdd("m",-12,{?Parameter}))&"/1/"&Year(DateAdd("m",-12,{?Parameter}))) to {?Parameter}

Entering 7/31/09 should return a range of 7/1/08-7/31/09.

With it grouped by the date_field and appropriate summaries, that should give you what you need.

 

[vega83]

Thank you so much for the reply but I am getting this error
BAD DATE FORMAT STRING:
formula was
{Command.ADDED_DT} in CDate(Month(DateAdd("m",-12,{?Date}))&"/1/"&Year(DateAdd("m",-12,{?Date}))) to {?Date}

 

[nobull613]

Sorry, that's because your PC probably has the default setting for numbers to include commas as thousand seperators and decimals. If that happens then it tries to set the year to 2,009 instead of the 2009 that's needed.

Try:
CDate(CStr(Month(DateAdd("m",-12,{?Date})),0,"")&"/1/"&CStr(Year((DateAdd("m",-12,{?Date})),0,''))

The CStr with the 0,'' sets it to zero decimal places and drops the thousand seperator.

 

[vega83]

nobull thats an excellent point
here is my new formula

{Command.ADDED_DT} in CDate(CSTR(Month(DateAdd("m",-12,{?Date})),0,'')&"/1/"
&Year(CSTR(DateAdd("m",-12,{?Date}))),0,'') to {?Date}

I get error called A DATE iS REQUIRED HERE hilighting
"CSTR(DateAdd("m",-12,{?Date}))"
Any Idea?, thanks a ton
V

 

[vega83]

Oh Sorry
{Command.ADDED_DT} in CDate(CSTR(Month(DateAdd("m",-12,{?Date})),0,'')&"/1/"
&Year(CSTR(DateAdd("m",-12,{?Date})),0,'')) to {?Date} here is right one and it gives error too many arguments have been goven to this function

 

[vega83]

I have just tweaked it to
{Command.ADDED_DT} in CDate(CSTR(Month(DateAdd("m",-12,{?Date})),0,'')&"/1/"
&CSTR(Year(DateAdd("m",-12,{?Date})),0,'')) to {?Date}
It works!, thanks a ton