FirstSortedValue

[Anonymous]

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?

[Digvijay_Singh]

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.

[Anonymous]

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

[dgreenberg]

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

[Anonymous]

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.

[dgreenberg]

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

[sunny_talwar · Accepted solution]

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.

[Anonymous]

Sorry, yes.

[Anonymous]

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.

[sunny_talwar]

No problem

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

Best,

Sunny