Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

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


 

 
  1. 21.2.2013
 

 

35


 

 


 

 

Andy


 

 
  1. 12.1.2013
 

 

10


 

 


 

 

Andy


 

 
  1. 12.2.2013
 

 

15


 

 


 

 

Mike


 

 
  1. 25.05.2013
 

 

30


 

 


 

 

John


 

 
  1. 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.

5 Replies
MVP
MVP

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.

See also attached.

Not applicable

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.2013Andy10
12.1.2013Andy15
21.2.2013John35
10.03.2013John15
25.05.2013Mike30

result

John15

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

MVP
MVP

Re: Selecting first available value of a sub set

Try

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

Not applicable

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

MVP
MVP

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.

Community Browser