Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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.

Any ideas please?

Thanks

1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

23 Replies
sinanozdemir
Specialist III
Specialist III

Hi Razor,

There you go:

Capture.PNG

Expression for active:

Capture.PNG

Expression for expired:

Capture.PNG

Hope this helps.

Not applicable
Author

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
Specialist III
Specialist III

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))

Not applicable
Author

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

Not applicable
Author

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.

Not applicable
Author

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.