Selecting first available value of a sub set

[#1]

I have a field saledate.

The use has the option to choose the too and from date of all the data.

I want a table that shows the sum(value) on the last available saledate, from the too
date that the user input.

I was looking at using set analysis: for saledate<toodate get saledate = max(saledate).

This date is different each value on my dimensions

This is the data

Sales   person	Saledate	Value
John	21.2.2013	35
Andy	12.1.2013	10
Andy	12.2.2013	15
Mike	25.05.2013	30
John	10.03.2013	15

If the user inputs the too date as 12.04.2013 then Mike should be excluded and only Johns sale on 10.03.2013 should be included.

And both  Andy’s sales should be included

The table should show:

Sales   Person	Value   on too date
Andy	25
John	15

I have tried various set analysis solutions but I’m not sure if I am getting the syntax right.

[swuehl]

Why should both of Andy's values be included? They show different dates, and you want only the latest date's sum, right?

You could try something like

=FirstSortedValue({<Saledate = {"<=$(vDate)"}>} aggr(sum(Value),Saledate), -Saledate)

in a chart with dimension Sales person.

See also attached.

[#3]

HI

Thanks for your help, sorry there was a typo both dates for Andy should have been the same, as there can be more than one value on a given date for a salesman. they need to be summed together.

This means by iusing the firstsorted value function nothing is returned for Andy.

Data

12.1.2013	Andy	10
12.1.2013	Andy	15
21.2.2013	John	35
10.03.2013	John	15
25.05.2013	Mike	30

result

John

15

I have tried to alter this formula by using th max() function to get the max date and sum the values over it but I am not having any luck still

[swuehl]

Try

FirstSortedValue({<Saledate = {"<=$(vDate)"}>}aggr(sum(Value),Saledate),-aggr(Saledate,Saledate))

[#5]

Hi

I can't seem to get it to work, so I've iuploaded a more accurate representation of my data into your demo and played around with it alittle but I am still having no luck.

I am stumpted as to where the problem is

[swuehl]

You need to set the format of your dates and numbers correctly in the script, so QV can interpret your values as numbers:

SET ThousandSep=',';

SET DecimalSep='.';

SET DateFormat='DD/MM/YYYY';

Then just set a Date in your input box that is larger than the min date in your data. Voila!

I've added another record for Andy, to see if the sum() works as expected.