topic Re: FirstSortedValue Help in QlikView

FirstSortedValue Help

Tue, 18 Feb 2014 21:59:32 GMT

Hi,

I'm having some trouble using FirstSortedValue and could use some help.

I have the following Tables: AccountInfo and BalanceInfo

AccountInfo looks like:

Key	Account#	EffectiveDate
12301012012	123	1/1/2012
12401012012	124	1/1/2012
12302012012	123	2/1/2012

Balanceinfo Looks like:

Key	BaLID	Balance	Status
12301012012	1	3.00	Open
12301012012	2	5.00	Open
12401012012	3	15.00	Open
12401012012	4	10.00	Open
12302012012	5	15.00	Open
12302012012	6	20.00	Closed
12302012012	7	30.00	Open

I have a 1:M relationship between Account and Balance Info. I'd like to be able to return the count and balances associated with the most recent open accounts. So for Account 123 I would return count of 2 and balance of 45. For account 124 I would want to return count of 2 and balance of 25.

I'm currently using sum((AGGR(firstsortedvalue({<Status={'Open'}>}Balance,-EffectiveDate),Account))) to calculate the total Balance, which seems to work, but I can't figure out how to determine the count.

I assumed using firstsortedvalue would be the way to go, but I am open to other suggestions.

Any help would be appreciated.

Re: FirstSortedValue Help

Tue, 18 Feb 2014 22:38:14 GMT

Hey Chris,

Its simple if you do the manipulations on the back-end.

Check out the attached file and see if it helps.

Thanks

Re: FirstSortedValue Help

Tue, 18 Feb 2014 23:27:43 GMT

Thanks. I need execute the expression on the front end though, as I would like the count / balance info to update base don user's selection.

Do you have any insight on how I could accomplish this on the front end?

Thanks,

Chris

Re: FirstSortedValue Help

MK_QSL — Wed, 19 Feb 2014 00:53:18 GMT

is it what you are looking for?

Re: FirstSortedValue Help

Wed, 19 Feb 2014 01:42:04 GMT

Not quite. for Account 123, only the most recent records (from 2/1//2012) should be displayed. With no selections the expression should ignore records from 1/1/2012 for account 123, and pick them up for Account 124.

Thanks,

Chris

Re: FirstSortedValue Help

Wed, 19 Feb 2014 01:58:38 GMT

What I did was just join the two tables as per your logic. It will change per users selection. The calculations are still made on the front end. All thats being done is just group by and join which shouldn't affect the results.

Can you give an example if you think its not working when user makes a selection?

Re: FirstSortedValue Help

Wed, 19 Feb 2014 02:19:45 GMT

I added a multi box containing effective date. When i select 40909 (1/1/2012) I would like to see Account 124 with a balance of 25 and count of 2, which your solution does. But based on my selction of 1/2/2012 I would like to see Account 123 with a count of 2 and balance of 8.

Essentially based on the users selection the balances associated with the most recent effect date should be displayed.

Re: FirstSortedValue Help

Wed, 19 Feb 2014 03:33:13 GMT

How do you get 8 when you select 2/1/2012. Can you please elaborate?

Re: FirstSortedValue Help

Wed, 19 Feb 2014 06:21:33 GMT

hi chris Please find Attachment i some what improved doc of Manish

hpoe this helps

Re: FirstSortedValue Help

Sat, 22 Feb 2014 19:12:33 GMT

I figured out the solution I was looking for.

here is what I used for the Balance

Firstsortedvalue(AGGR(sum({<Status={'Open'}>}Balance),EffectiveDate,Account),-AGGR((EffectiveDate), Account, EffectiveDate)

and count:

Firstsortedvalue(AGGR(Count({<Status={'Open'}>}Balance),EffectiveDate,Account),-AGGR((EffectiveDate), Account, EffectiveDate)

Thanks very much for your help and suggestions!

Updated 3/5/14 per Manish's comments. I mistakenly omitted that Status should be Open.

Re: FirstSortedValue Help

Sun, 23 Feb 2014 09:44:31 GMT

hi chris,

if u found Solution then please mark this Discussion as answered

so it will help those who are facing same type of problem

thanks And regards

Re: FirstSortedValue Help

MK_QSL — Mon, 03 Mar 2014 06:44:55 GMT

We are glad that you found your solution.

Just for curiosity, what was wrong in our solution?

After checking your solution, found that it will not work for Status Open as your have given generalized answer and nowhere mentioned Status = OPEN !!!

Can you check our solutions also and match with your one so that you can identify what was wrong /