need suggestion regarding to set analysis

[arulsettu]

Hi

     my scenario is if i select 12th month  i need to calculate values from 0 to 6th month

because my month field like 0,1,2,3,4,....12

i have year and month field only.  no date field

Sum({$<ACNT_FLEX_02={'Balance Sheet'},TBAL_CAL_MONTH=,TBAL_CAL_YEAR=, datefield={">=$(=date(Addmonths(max(datefield),-11)))<=$(=date(Addmonths(max(datefield),-6)))"}>}TBAL_MTD_DR_LC_1-TBAL_MTD_CR_LC_1)

the above code is giving result for 1/1/2014 to 6/1/2014

but i need to add values from 0th month also

so i did like this

=sum({<ACNT_FLEX_02={'Balance Sheet'},TBAL_CAL_MONTH={'0'}>}TBAL_MTD_DR_LC_1-TBAL_MTD_CR_LC_1)+Sum({$<ACNT_FLEX_02={'Balance Sheet'},TBAL_CAL_MONTH=,TBAL_CAL_YEAR=, datefield={">=$(=date(Addmonths(max(datefield),-11)))<=$(=date(Addmonths(max(datefield),-6)))"}>}TBAL_MTD_DR_LC_1-TBAL_MTD_CR_LC_1)

but i am not getting the correct result. any one can please find what i am doing wrong?

its really urgent,,

thanks

[Anonymous]

Sum({$<ACNT_FLEX_02={'Balance Sheet'},TBAL_CAL_MONTH=,TBAL_CAL_YEAR=, datefield={">=$(=date(Addmonths(max(datefield),-12)))<=$(=date(Addmonths(max(datefield),-6)))"}>}TBAL_MTD_DR_LC_1-TBAL_MTD_CR_LC_1)

[arulsettu]

HI,

     if change to 12 means the date showing between 12/1/2013 to 6/1/2014

thanks

[Anonymous]

What is the 0th month?

[arulsettu]

its opening balance for the year

[arulsettu]

does any one know about this please help.

its urgent

thanks

[Anonymous]

Will it not work if you use the month field in your expression instead of date? Also, what should be the result when you select other  months?

[arulsettu]

i dont have date field instead of that i created date filed using month and year

expected result is

if i select 2014 12th month TBAL_COMP_CODE = 001

Cash and cash equivalents                                                      

964962549  

Assets                                                                         

Insurance and other receivables                                            

-2.155E+08    

Assets                                                                         

Investments                                                      

4.5878E+09

thanks

[arulsettu · Accepted solution]

i got answer

=sum({<ACNT_FLEX_02={'Balance Sheet'},TBAL_CAL_MONTH={'0'}>}TBAL_MTD_DR_LC_1-TBAL_MTD_CR_LC_1)+Sum({$<ACNT_FLEX_02={'Balance Sheet'},TBAL_CAL_MONTH=,TBAL_CAL_YEAR=, datefield={">=$(=date(Addmonths(max(datefield),-11)))<=$(=date(Addmonths(max(datefield),-5)))"}>}TBAL_MTD_DR_LC_1-TBAL_MTD_CR_LC_1)