Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

santhosam
New Contributor

Month on Month closing issue

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...

5 Replies
udit_kumar_sana
Contributor II

Re: Month on Month closing issue

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
New Contributor

Re: Month on Month closing issue

I want to get generate the details on Script.

MVP
MVP

Re: Month on Month closing issue

Hi,

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

Regards,

Jagan.

santhosam
New Contributor

Re: Month on Month closing issue

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

MVP
MVP

Re: Month on Month closing issue

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.

Community Browser