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

[sinanozdemir]

Hi Razor,

There you go:

Expression for active:

Expression for expired:

Hope this helps.

[#3]

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

[sinanozdemir]

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.

[Digvijay_Singh]

Try this..

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

[#6]

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

[#7]

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

[Digvijay_Singh]

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.

[#9]

Hi again,

I have attached a copy with new data.

Any help on this would be appreciated.

Thanks

[Digvijay_Singh]

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.