23 Replies Latest reply: Aug 31, 2015 2:10 AM by Sunny Talwar

# Sum and FirstSortedvalue issue

Hi all,

I have a chart that has one dimension called PID.

I have several expressions (5) in total that are of the form

Cost = Firstsortedvalue({<YearMonth = >} Cost, -StartDate)

Status = Firstsortedvalue({<YearMonth = >} Status, -StartDate)

I would only like to have one row for each PID and the firstsorted value gives me the latest row as expected.

I am trying to do a sum in two textboxes to display the costs for all "Expired" statuses one for all "Active" statuses and I am unable to get it to work.

I have attached my table with some comments to make it easier to understand.

Thanks

• ###### Re: Sum and FirstSortedvalue issue

Hi Razor,

There you go:

Expression for active:

Expression for expired:

Hope this helps.

• ###### Re: Sum and FirstSortedvalue issue

Hi,

Thanks for this.

This works, but keep in my mind, my original table chart has rows with repeated PIDs.

i.e PID Cost Status

1    5       Inactive

1    10     Inactive

1    15     Active

The firstsorted value is what cleaned the data to only give me the latest value i.e. a row like

PID Cost Status

1     15     Active

Your expression is doing a sum of the active or expired but it doesn't take into account that I only want to sum the values for the latest row.

I hope this makes sense.

Thanks again for your help so far

• ###### Re: Sum and FirstSortedvalue issue

Well, if your problem is more complex than this, then I suggest you post a more comprehensive data-set. Although, one possible solution could be changing the set analysis to the below one:

='Active: ' & Sum({<Status = {"=Aggr(FirstSortedValue(Status,-[Start Date]), PID)='Active'"}>}Cost)

As a result of this, you aggregating FirstSortedValue by PID and getting the latest record per PID.

Hope this helps.

• ###### Re: Sum and FirstSortedvalue issue

Hi Sinan,

Thanks for your help so far.

When I add the aggr part in red, it results in 0. I think the expression doesn't evaluate correctly for the status

• ###### Re: Sum and FirstSortedvalue issue

Try this..

='Cost with Expired Status : '& Sum({<Status={'Expired'}>}Aggr(FirstSortedValue(Cost,-[Start Date]),PID))

• ###### Re: Sum and FirstSortedvalue issue

Hi Digvijay,

Your answer should give me exactly what I want, but I have noticed that for some statuses it gives me the right sum and for other statuses, the sum is wrong.

Apart from that, your expression seems almost perfect.

Thanks

• ###### Re: Sum and FirstSortedvalue issue

It would be great if you can share the sample test data where you are getting wrong results, I just tested thru attached test file but couldn't generate the problem you have mentioned.

• ###### Re: Sum and FirstSortedvalue issue

Hi again,

I have attached a copy with new data.

Any help on this would be appreciated.

Thanks

• ###### Re: Sum and FirstSortedvalue issue

I am still not able to figure out, also in your new data I didn't see duplicate PID with old/new start dates. I tried to match with Excel and the QVW it is showing equal distribution. Let me know thru attached QVW where we have mismatch. Thanks.

• ###### Re: Sum and FirstSortedvalue issue

Forgot to change the label for last text box, that is for 'Unknown End Date'. Thanks

• ###### Re: Sum and FirstSortedvalue issue

Thanks again.

I have now attached the raw file which has multiple rows per PID.

If you can have a look again at your convenience, I would really appreciate it.

Thank you

• ###### Re: Sum and FirstSortedvalue issue

This works perfectly on your data -

Sum(Aggr(FirstSortedValue({<Status={'Expired'}>} Cost,-StartDate),PID))

But I couldn't figure out why it doesn't work after making any selection, see the attached QVW.

• ###### Re: Sum and FirstSortedvalue issue

just add 1 in the Sum() function:

Sum({1}Aggr(FirstSortedValue({<Status={'Expired'}>} Cost,-StartDate),PID))

It will ignore the selections.

• ###### Re: Sum and FirstSortedvalue issue

Thanks again.

I have attached a QVW with what I am looking for.

Your sums are correct, but the way it is summing up the values isn't the way I want it to sum the values.

For example, if one PID has expired 3 times in the past and now has one active, then it shouldn't sum anything under expired at all as it is now active.

I have attached a QVW and a spreadsheet. If you filter on the spreadsheet for the relevant statuses and do a sum, those are the values I expect to see in my textbox.

Hope this makes sense.

Thanks again for all your help

• ###### Re: Sum and FirstSortedvalue issue

I am calling an expert stalwar1, I am sure he will solve this immediately.

• ###### Re: Sum and FirstSortedvalue issue

Hey Sinan

Thanks for considering me as an expert , but I am just learning like you. I have proposed a solution, hopefully it helps.

Best,

Sunny

• ###### Re: Sum and FirstSortedvalue issue

Thanks Sunny for coming to help.

As always, an awesome solution.

Thanks again.

• ###### Re: Sum and FirstSortedvalue issue

No problem Sinan

• ###### Re: Sum and FirstSortedvalue issue

This expression seems to work for expired. Try it out:

=Sum({1}Aggr(If(FirstSortedValue(Status,-StartDate) = 'Expired', FirstSortedValue({<Status={'Expired'}>} Cost,-StartDate)),PID))

May need to modify the set analysis for selection requirement, but without selection it gives 24,109

HTH

Best,

Sunny

• ###### Re: Sum and FirstSortedvalue issue

Thanks so much for this.

It is exactly what I want.

Thanks also to Sinan and Digvijay for all your help.

I really appreciate it.

• ###### Re: Sum and FirstSortedvalue issue

Awesome

Best,

Sunny

• ###### Re: Sum and FirstSortedvalue issue

These expression also seem to work:

Expired:

=Sum({1}Aggr(FirstSortedValue({<PID={"=FirstSortedValue(Status,-StartDate) = 'Expired'"}>} Cost,-StartDate),PID))

Active:

=Sum({1}Aggr(FirstSortedValue({<PID={"=FirstSortedValue(Status,-StartDate) = 'Active'"}>} Cost,-StartDate),PID))

Unknown End Date:

=Sum({1}Aggr(FirstSortedValue({<PID={"=FirstSortedValue(Status,-StartDate) = 'Unknown End Date'"}>} Cost,-StartDate),PID))

• ###### Re: Sum and FirstSortedvalue issue

Hi Razor,

As Digvijay Singh indicated there are no duplicate values in the spreadsheet. If this is case then, you don't even need to use FirstSortedValue() at all.

Let us know if you have any further questions or samples.

Thanks