topic Re: question in set analysis in QlikView

question in set analysis

Mon, 11 Jul 2011 08:53:49 GMT

please i have a problem

i have 3 variables vBeforeDate and vStartDate and vEndDate

and 5 columns : CompanyName, Date, Debit, Credit, Balance

i want when i enter date in vBeforeDate variable tyhe balances till this date are summed and this is balance before this date

for dates after this one (in the beriod of vStartDate and vEndDate)

the balance = balance before+debit-credit

ex:

balance before 1/1/2011 was 400

and i have 2 dates after that

30/1/2011 debit=10 credit= 20 so balance should be 400+10-20=390

and

5/2/2011 debit=100 credit= 30 so balance should be the last balance which is 390+debit-credit ....> 390+100-30=460

so the problem is how to add to each row the balnce of the before row

The attachment below can clarify my problem

question in set analysis

kaushiknsolanki — Mon, 11 Jul 2011 08:58:31 GMT

Hi,

Can you please provide the qvw file.

Regards,

Kaushik Solanki

Re: question in set analysis

Mon, 11 Jul 2011 09:06:44 GMT

Here it's ..

question in set analysis

kaushiknsolanki — Mon, 11 Jul 2011 09:34:01 GMT

Hi,

Here is a set expression which will give you the balance between the startdate and enddate.

sum({1<Date = {">=$(#vStartDate)<=$(#vEndDate)"},Year = ,Month =>}Debit -Credit)

You can use the same for getting balance before date.

Meaning enter the startdate as 0 and end date as the date before which you want the balance.

Regards,

Kaushik Solanki

Re: question in set analysis

Mon, 11 Jul 2011 09:52:27 GMT

Many Thanks for ur help

but i know this is to get the balance between 2 dates

but what i want is to add the balance before to be the new balance

i mean each row the balance of the above raw is added to the new calcualtion

i know how to makle this but in the first raw i want to add expression and in other raws another expression

like the example i explained above in the main thread

question in set analysis

kaushiknsolanki — Mon, 11 Jul 2011 09:57:13 GMT

Hi,

The answer to your question

"i know how to makle this but in the first raw i want to add expression and in other raws another expression"

is that you can use the if statement, say

if(rowno() = 1, this expression, other expression)

Regards,

Kaushik Solanki

Re: question in set analysis

Mon, 11 Jul 2011 11:09:38 GMT

Thanks Alot

i write it but the result is not OK

can you Please see attached qvw file and expressions in it

Re: question in set analysis

Mon, 11 Jul 2011 12:34:52 GMT

i answered it

i got the idea from this thread and enhanced it

http://community.qlik.com/message/82992#82992

here is the final solution