topic Re: Need Expression? in QlikView

Need Expression?

khadeer — Wed, 30 Jan 2013 07:28:09 GMT

Hi Everyone,

I have Closing Balance for every year i.e for suppose for march 2011(financial year)

Let it be 5000

And every month we have value, Suppose

Apr= 500

May= 100

June= -200

when i am calculating result my formula is Closing balance of that year or opening balance of present year( 5000) + Month Value(Respective Months)

then for Apr Mnth my closing is 5000+500 = 5500

May 5500+100= 5600

June 5600+(-200)=5400

This value i am getting, but if iselcted Apr+ May .... My result should be Apr Opening(5000)+May Closing(5600)= 10600

Like this if i selected May n June 5500+5400= 10900

How i can do this in dynamically. Help me regarding this.

Re: Need Expression?

Wed, 30 Jan 2013 07:47:37 GMT

try this

=openingbalence + sum({<month = { "> = $(=only(month)"}>}amount)

hope this helps you

Re: Need Expression?

khadeer — Wed, 30 Jan 2013 07:58:09 GMT

Hi Vishwarajan,

Its not working.

Re: Need Expression?

er_mohit — Wed, 30 Jan 2013 09:34:59 GMT

hi see the attached file

Re: Need Expression?

khadeer — Wed, 30 Jan 2013 09:59:15 GMT

Hi iamqvsap

for 3 months, suppose if i selected Apr, May, June

5000+5400= 9400

if selected May June July then(let June= 300)

5600+5700= 11300

Re: Need Expression?

Wed, 30 Jan 2013 10:16:30 GMT

If i understood your post ,it seems like we should take maxumum and second maximum month for the calculation (if 3 months are selected).

= sum({$<MonthId= {$(=max(MonthId,2))} >}OpeningBalance)

sum({$<MonthId= {$(=max(MonthId))} >}ClosingBalance)

Re: Need Expression?

khadeer — Wed, 30 Jan 2013 10:45:41 GMT

Its not working

Re: Need Expression?

khadeer — Wed, 30 Jan 2013 11:04:24 GMT

Hi iamqvsap,

we r near to that, but i need first selected month means if user selects Apr, May Jun... it dynamically take first one means Apr instead 2 in that expression.

= sum({$<MonthId= {$(=max(MonthId,2))} >}OpeningBalance)

Re: Need Expression?

khadeer — Wed, 30 Jan 2013 11:25:28 GMT

Anyone is there to solve this issue.

Re: Need Expression?

Wed, 30 Jan 2013 12:21:04 GMT

If you want first month of the selected period, then you go here

sum({$<MonthId= {$(=min(MonthId))} >}OpeningBalance)

sum({$<MonthId= {$(=max(MonthId))} >}ClosingBalance)