topic Re: Month on Month closing issue in QlikView

Month on Month closing issue

santhosam — Sat, 23 Mar 2013 04:36:25 GMT

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

Re: Month on Month closing issue

udit_kumar_sana — Sat, 23 Mar 2013 05:18:06 GMT

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

Re: Month on Month closing issue

santhosam — Sat, 23 Mar 2013 05:45:46 GMT

I want to get generate the details on Script.

Re: Month on Month closing issue

jagan — Sat, 23 Mar 2013 07:10:38 GMT

Hi,

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

Regards,

Jagan.

Re: Month on Month closing issue

santhosam — Sat, 23 Mar 2013 07:29:43 GMT

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

Re: Month on Month closing issue

jagan — Sat, 23 Mar 2013 08:40:49 GMT

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.