Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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...
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
I want to get generate the details on Script.
Hi,
In script try using Previous() for this, before that you need to order the data by the required dimensions.
Regards,
Jagan.
Thanks a lot for ur reply i obtained the solution by peek function and whille loop to generate date for my key.
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.