topic Re: Sum and FirstSortedvalue issue in QlikView

Sum and FirstSortedvalue issue

Thu, 27 Aug 2015 14:37:44 GMT

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

Re: Sum and FirstSortedvalue issue

sinanozdemir — Thu, 27 Aug 2015 15:28:21 GMT

Hi Razor,

There you go:

Expression for active:

Expression for expired:

Hope this helps.

Re: Sum and FirstSortedvalue issue

Thu, 27 Aug 2015 16:21:33 GMT

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

sinanozdemir — Thu, 27 Aug 2015 17:08:54 GMT

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

Digvijay_Singh — Thu, 27 Aug 2015 17:59:38 GMT

Try this..

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

Re: Sum and FirstSortedvalue issue

Fri, 28 Aug 2015 08:47:04 GMT

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

Fri, 28 Aug 2015 08:48:51 GMT

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

Digvijay_Singh — Fri, 28 Aug 2015 09:20:14 GMT

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

Fri, 28 Aug 2015 10:31:35 GMT

Hi again,

I have attached a copy with new data.

Any help on this would be appreciated.

Thanks

Re: Sum and FirstSortedvalue issue

Digvijay_Singh — Fri, 28 Aug 2015 11:27:37 GMT

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

Digvijay_Singh — Fri, 28 Aug 2015 11:30:03 GMT

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

Re: Sum and FirstSortedvalue issue

sinanozdemir — Fri, 28 Aug 2015 12:43:46 GMT

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

Re: Sum and FirstSortedvalue issue

Fri, 28 Aug 2015 13:01:57 GMT

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

Digvijay_Singh — Fri, 28 Aug 2015 14:42:55 GMT

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

sinanozdemir — Fri, 28 Aug 2015 14:47:04 GMT

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

Fri, 28 Aug 2015 16:04:33 GMT

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

sinanozdemir — Fri, 28 Aug 2015 19:30:08 GMT

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

Re: Sum and FirstSortedvalue issue

sunny_talwar — Sat, 29 Aug 2015 07:45:18 GMT

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

sunny_talwar — Sat, 29 Aug 2015 07:46:47 GMT

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

Sat, 29 Aug 2015 21:42:46 GMT

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.