calculate previous six month value

[arulsettu]

hi

i need to calculate previous six month value so i used below code

sum({<ACNT_FLEX_02={'Balance Sheet'},TBAL_CAL_MONTH={'>=$(vTBL_month)'},TBAL_CAL_YEAR={'>=$(Acnt_year)'}>}TBAL_MTD_DR_LC_1-TBAL_MTD_CR_LC_1)

vTBL_month=max(TBAL_CAL_MONTH)-6

Acnt_year=max(TBAL_CAL_YEAR)

TBAL_CAL_MONTH contains value like this

0,1,2,3,4,5,6,7,8,9,10,11,12

what i need is if i select 12th month it should show 6th month value. if i select 6th month it should show previous year 12th month value.

plz anyone suggest some idea

thanks

[sushil353]

Hi,

It would be better if you use date field contain month and year together and then use addmonths function

vmonth = Addmonths(Date,-6)

if you don't have date field then you can use makedate function to create the date as makedate(2001,5)

HTH

sushil

[sagarkharpude]

Create the variable as

vPrior6=date(AddMonths(MonthYear,-6),'YYYY-MM')

And use it set analysis as below

sum({$<ACNT_FLEX_02={'Balance Sheet'},YearMonth={$(=vPrior6)}>}TBAL_MTD_DR_LC_1-TBAL_MTD_CR_LC_1)

Create the YearMonth field in script.

[arulsettu]

Hi

     i dont have date field. only TBAL_CAL_MONTH and TBAL_CAL_YEAR

can you give the solution

thanks for replying

[arulsettu]

its showing only zeros

thanks

[sagarkharpude]

share sample app.

[Anonymous]

You can make a date field using the  makedate() function in the script.

[arulsettu]

here is the sample app

[arulsettu]

yes i created like this

MakeDate(TBAL_CAL_YEAR,TBAL_CAL_MONTH) as datefield

[arulsettu]

created variable like this vPrior6=  AddMonths(datefield,-6)

in chart used this

sum({$<ACNT_FLEX_02={'Balance Sheet'},MonthYear={$(=vPrior6)}>}TBAL_MTD_DR_LC_1-TBAL_MTD_CR_LC_1)

but getting zero only

plz suggest something