topic Re: FirstSortedValue in QlikView

FirstSortedValue

Anonymous — Thu, 17 Sep 2015 09:36:01 GMT

Quik question, hopefully.

I'm trying to get the value for the last month for a branch, which is working fine if i don't have a branch selected.

What i'm trying to get is a step further, where if i select the branch it displays for all the branches in that area.

=FIRSTSORTEDVALUE({<CI_Counter = {'1'}>}AGGR(SUM({<Branch=, Area=p(Area)>} Actual_YTD_Cost),Date,Branch),-aggr(Date,Branch, Date))

The above is retuning the branch only.

Any ideas?

Re: FirstSortedValue

Digvijay_Singh — Thu, 17 Sep 2015 10:06:22 GMT

Can you share some sample data to understand the problem better?

You have used <Branch=, to disregard any selection in the branch, I don't yet understand the issue but check if that is required or not. Also I didn't understand Date twice in second line aggr function.

Re: FirstSortedValue

Anonymous — Thu, 17 Sep 2015 10:18:04 GMT

Thanks for the reply.

Sensitive Data unfortunately, so unable to share, but basically:

Branch Month Cost
1 01/01/2015 100

1 01/01/2015 150

1 01/02/2015 200

2 01/01/2015 100

2 01/01/2015 125

2 01/02/2015 200

3 01/01/2015 100

3 01/01/2015 110

3 01/02/2015 150

The idea being i'm after the sum for the max month per branch.

The logic was assisted from an old post, and works where i have no branch selected.

But my need is where i have selected a branch, i want to see all branches in that area, which works fine, except when used with the firstsortedvalue

Re: FirstSortedValue

dgreenberg — Thu, 17 Sep 2015 14:29:08 GMT

I am sorry I can't follow what you mean by all brances in that area. Does that mean all branches with the same #??

Re: FirstSortedValue

Anonymous — Thu, 17 Sep 2015 14:40:04 GMT

There can be multiple branches to an area. So if I select branch a, which

is in area 1, I want to return all branches total sum for the max month.

Re: FirstSortedValue

dgreenberg — Thu, 17 Sep 2015 14:45:56 GMT

So I should assume there is another column in your data called area?

Re: FirstSortedValue

sunny_talwar — Thu, 17 Sep 2015 14:46:20 GMT

If each of the branch will have the same max month then you can try this:

Dimension: Branch

Exrpression: Sum({<Month = {"$(=Date(Max(Month), 'DD/MM/YYYY'))"}>}Cost) // Assuming Month is in DD/MM/YYYY format.

Re: FirstSortedValue

Anonymous — Thu, 17 Sep 2015 14:49:04 GMT

Sorry, yes.

Re: FirstSortedValue

Anonymous — Fri, 18 Sep 2015 08:26:16 GMT

Thanks Sunny. I did look at this approach first, but it wasn't being affected by the MAX DATE. Specifying the format seems to have been the missing link.

Re: FirstSortedValue

sunny_talwar — Fri, 18 Sep 2015 10:07:42 GMT

No problem

I am glad we were finally able to figure it out.

Best,

Sunny