Month on Month closing issue

[santhosam]

I had an issue, I want to calculate month on month closing value on branchwise and Itemwise and hence i used peek function to obtain the Closing Value. But i faced an Issue with that, Which i only can able to obtain the closing balance in which if any transaction available, I want to carry forward it to next month also, Can any one help me...

[udit_kumar_sana]

Hi ,

You try below expressions :

for Current Month Calcultion:-

if(IsNull(sum({$<MONTH={"$(=Monthname(max(DATE)))"},DATE=,DAY=,QTR=>}value)),

sum({$<MONTH={"$(=monthname(max({<DATE= {'<=$(vMaxDate1)'},MONTH=,DAY=,QTR=>}DATE)))"},DATE=,DAY=,QTR=>}value) 
,
sum({$<MONTH={"$(=Monthname(max(DATE)))"},DATE=,DAY=,QTR=>}value)  )

for Previous Month i.e currentmonth-1:-

if(IsNull(sum({$<MONTH={"$(=Monthname(addmonths(max(DATE),-1)))"}value))  ,

sum({$<MONTH={"$(=monthname(max({<DATE = {'<=$(vMaxDate1)'},
MONTH=,DAY=,QTR=>}DATE)))"},DATE=,DAY=>}value) 
,sum({$<MONTH={"$(=Monthname(addmonths(max(DATE),-1)))"}>}value))

similarly you can calculate for next month i.e currentmonth-2,currentmonth-3...

here variable vMaxDate1=MonthStart(Max(LINK_TABLE_DATE)),

this will give the data for the month only were transactions are available,if the current month is not having any transactions.

Hope this all will work for you.

Regards,

Udit

[santhosam]

I want to get generate the details on Script.

[jagan]

Hi,

In script try using Previous() for this, before that you need to order the data by the required dimensions.

Regards,

Jagan.

[santhosam]

Thanks a lot for ur reply i obtained the solution by peek function and whille loop to generate date for my key.

[jagan]

Hi,

Using While it will take more time loading, if possible avoid while loop, instead try WHILE in Load statement.

Hope this helds you.

Regards,

Jagan.