FirstSortedValue issue

[Anonymous]

Hey all,

I'm having difficulty getting values using FirstSortedValue.  I'm trying to retrieve the latest "amount" per record but I am getting null values and I'm not sure why. Here's what my script looks like:

=FirstSortedValue(amount, -date)

I would greatly appreciate the help.

Thanks,

Napac

*EDIT*

I feel I should clarify what I'm looking for.  I'm trying to extract the latest total amount per account.

Account                             Amount                    Date

1                                        25                           4/30/13

2                                       432                          4/30/13

2                                       324                          4/30/13

2                                       381                          5/1/13

1                                       89                            5/1/13

1                                       9821                        5/1/13

2                                       423                          5/2/13

For instance, I would want to sum the latest amounts for accounts 1 and 2 without bringing in any amounts before the latest date

[Anonymous]

Napac,

The expression looks fine.  The problem could be because of the relations between amount and date.  Are they in the same logical table?  Is there an amount for every date?  Are there multiple amount values in one date?  How many amount values available if you select the latest date?

Regards,

Michael

[Anonymous]

There are multiple values for the data, I'm trying to sum the values for the latest date per account

[Anonymous]

I've also tried Last value as such:

table_2:

load

*,

LastValue("total_amount") as Latest_total

resident table_1;

[anandathome]

Hi Napac,

      Could you check what format you date is in?

Convert it to a Numeric value like 20130502 for 02-May-2013

and check.

Regards,

Anand.

[Anonymous]

Still nothing 😕

I feel I should clarify what I'm looking for.  I'm trying to extract the latest total amount per account.

Account                             Amount                    Date

1                                        25                           4/30/13

2                                       432                          4/30/13

2                                       324                          4/30/13

2                                       381                          5/1/13

1                                       89                            5/1/13

1                                       9821                         5/1/13

For instance, I would want to sum the latest amounts for accounts 1 and 2 without bringing in anything before the latest date

[rwunderlich]

Under the rules of FIrstSortedValue, you will get null when the more than one row is returned. So for example you will get null for account 1 because it has two rows for the last date - 5/1/13. You can fix that in two ways.

1. Add the distinct keyword

=FirstSortedValue(DISTINCT amount, -date)

I'm not sure what the rules are for how distinct decides which row to pick.

2. Add a "bias" to force the sort-weights to be unequal"

=FirstSortedValue(DISTINCT amount, -date + (amount*.00001))

That will pick the largest value on the last date.

If you want to select the last row in load order, asign a "recno() as recid" counter to the table at load time and use

=FirstSortedValue(DISTINCT amount, -date + (recid*.00001))

-Rob

[rustyfishbones]

Maybe this video on YouTube could help

http://youtu.be/at9riDWED4Q

Regards

Alan

[#9]

LOAD Account,

     Date(max(Date1),'DD-MM-YYYY') as maxdate,

     FirstSortedValue(Amount,-Date1) as Total_Amount

     Where Account <=2

     Group BY Account;

LOAD * INLINE [

    Account, Amount, Date1

    1, 25, 4/30/13

    2, 432, 4/30/13

    2, 324, 4/30/13

    2, 381, 5/1/13

    1, 89, 5/1/13

    1, 9821, 5/1/13

    2, 423, 5/2/13

];

[swuehl · Accepted solution]

Hi Napac,

I read your request that you want to group your data per Account and Date, but only for the latest date per Account.

I think you can do it like this:

Create a straight table chart with dimension Account, and then create an expression like

=FirstSortedValue( aggr(sum(Amount),Account,Date), -aggr(Date,Account,Date) )

You can create an additional expression

=max(Date)

to show also the latest date per Account in the chart.

Take care that your Date field is interpreted by QV as Date, having a numerical representation.

See also attached for a sample.