Sum and FirstSortedvalue issue

[#1]

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.

Any ideas please?

Thanks

[Digvijay_Singh]

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

[sinanozdemir]

Hi Razor,

As Digvijay 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

[#4]

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

[Digvijay_Singh]

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.

[sinanozdemir]

just add 1 in the Sum() function:

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

It will ignore the selections.

[#7]

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

[sinanozdemir]

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

[sunny_talwar · Accepted solution]

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

[sunny_talwar]

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

[#11]

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.