topic Re: Getting first value in the selected Date Range in QlikView

Getting first value in the selected Date Range

Wed, 11 Jan 2012 06:36:24 GMT

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

Getting first value in the selected Date Range

CELAMBARASAN — Wed, 11 Jan 2012 07:22:55 GMT

Hi,

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

Celambarasan

Re: Getting first value in the selected Date Range

Miguel_Angel_Baeyens — Wed, 11 Jan 2012 08:00:22 GMT

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

Re: Getting first value in the selected Date Range

Wed, 11 Jan 2012 22:26:01 GMT

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

Re: Getting first value in the selected Date Range

Wed, 11 Jan 2012 22:26:21 GMT

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:

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

Re: Getting first value in the selected Date Range

Miguel_Angel_Baeyens — Wed, 11 Jan 2012 23:26:09 GMT

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

Re: Getting first value in the selected Date Range

Wed, 11 Jan 2012 23:47:43 GMT

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

Re: Getting first value in the selected Date Range

Miguel_Angel_Baeyens — Thu, 12 Jan 2012 00:03:06 GMT

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

Re: Getting first value in the selected Date Range

Anonymous — Thu, 12 Jan 2012 01:58:43 GMT

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.