5 Replies Latest reply: Jun 20, 2013 6:05 AM by Stefan Wühl

# Selecting first available value of a sub set

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.

• ###### Re: Selecting first available value of a sub set

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.

• ###### Re: Selecting first available value of a sub set

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

• ###### Re: Selecting first available value of a sub set

Try

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

• ###### Re: Selecting first available value of a sub set

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

• ###### Re: Selecting first available value of a sub set

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.