Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

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

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

23 Replies
sinanozdemir
Valued Contributor III

Re: Sum and FirstSortedvalue issue

Hi Razor,

There you go:

Capture.PNG

Expression for active:

Capture.PNG

Expression for expired:

Capture.PNG

Hope this helps.

Not applicable

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

sinanozdemir
Valued Contributor III

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.

Digvijay_Singh
Honored Contributor III

Re: Sum and FirstSortedvalue issue

Try this..

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

Not applicable

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

Not applicable

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

Digvijay_Singh
Honored Contributor III

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.

Not applicable

Re: Sum and FirstSortedvalue issue

Hi again,

I have attached a copy with new data.

Any help on this would be appreciated.

Thanks

Digvijay_Singh
Honored Contributor III

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.

Community Browser