Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
santhosam
Contributor
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
Creator II
Creator II

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

I want to get generate the details on Script.

jagan
Luminary Alumni
Luminary Alumni

Hi,

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

Regards,

Jagan.

santhosam
Contributor
Contributor
Author

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

jagan
Luminary Alumni
Luminary Alumni

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.