Getting first value in the selected Date Range

[#1]

I want to get the first and last value in the selected date range. How I can achive this?

[CELAMBARASAN]

Hi,

     Little more brief about selected date range? first and last value of what?

Celambarasan

[Miguel_Angel_Baeyens]

Hi,

Assuming you have selected several dates from in a listbox, say DateField, the following functions will return the highest and the lowest value for the selections respectively:

=Date(Max(DateField))

and

=Date(Min(DateField))

Use a text object to check that the values returned are right. Date() is needed because Max() and Min() will return a numeric value like 40174, instead of "29/12/2009". The Date() function will do that.

Hope that helps.

Miguel

[#4]

Ok Miguel, What you sugested is correct and I tryed the same thing but this is not what I want let me give you an eg:

for eg if we have selected date from 01/01/2012 to 31/01/2012 and want all data with in the range then if our data set do not contain any data for 01/01/2012 and starts with 02/01/2012 then your function "=Date(min(datefield))" will return 02/01/2012 but I need 01/01/2012.

or in other words is there any way in qlik view to read the sheet object value. something like

=min(sheetobject.selectedvalue)

Regards,

Aggyey Mishra

[#5]

Ok Miguel, What you sugested is correct and I tryed the same thing but this is not what I want let me give you an eg:

for eg if we have selected date from 01/01/2012 to 31/01/2012 and want all data with in the range then if our data set do not contain any data for 01/01/2012 and starts with 02/01/2012 then your function "=Date(min(datefield))" will return 02/01/2012 but I need 01/01/2012.

or in other words is there any way in qlik view to read the sheet object value. something like

=min(sheetobject.selectedvalue)

Regards,

Aggyey Mishra

[Miguel_Angel_Baeyens]

Hi Aggyey,

It depends on how you have built the calendar. If you have no values linked to date "01/01/2012" first, user should not be able to select it (and if you use a calendar object you will see how it starts on "02/01/2012" even when the calendar shows the 01/01/2012). Anyway, you can use another aggregation function instead of Min and Max:

Aggr(Min(Date), TransactionID)

That will return the lowest value in Date field for each TransactionID value.

If you are using that in a chart, you can use FirstSortedValue() or Aggr() functions to get the max and min values for each value in the dimension field.

Hope that helps.

Miguel

[#7]

OK, Here it is in more detail.

For eg origanal data set is as follows:

Name          Sales          Date

x                    2               01/02/2010

x                    2               02/01/2012

y                    1               01/01/2011

x                    3               04/01/2012

y                    2               15/01/2012

Now, If user have selected the date range from 01/01/2011 to 15/01/2012 then on qlikview I want to show

Name          sales          DateFrom          DateTo

x                    5             01/01/2011     15/01/2012

y                    3             01/01/2011     15/01/2012

Now this is what I am trying to get.

I hope I will get the right anwser this time. Thankyou very much Miguel.       

Thanks and Regards,

Aggyey Mishra 

[Miguel_Angel_Baeyens · Accepted solution]

Hi Aggyey,

Check the attached application that returns as expected. I'm using two variables to set the range and using them in the expressions of the chart, to return the sum of sales bewteen those dates. In a new straight table, using Names as dimension, this is the expression for Sales

Sum({< Date = {'>=$(=Date(vStart))<=$(=Date(vFrom))'} >} Sales) 

And using Max() and Min() will get the highest and lowest value of Date corresponding to each value in Name, not the whole range that is already shown in the variables. In the case of DateFrom

Min({< Date = {'>=$(=Date(vStart))<=$(=Date(vFrom))'} >} Date) 

And likewise for DateTo using Max() instead.

Hope that helps with set analysis and understanding date ranges.

Miguel

[Anonymous]

Great discussion guys and thanks Aggy for marking it as answered. I'm moving this over to the QV Developers area as you will get more visibility.