How to Pick latest time period

[#1]

Dear Experts,

I need your help, how can I pick/select latest time periods from the each format listed below same column

example time formats in my data

1 indicator with following formats :

2001-02

2002-03

2004-05

2008-09

I want to pick : 2008-09 from above

Another indicator with following type type:   answer - 2010/11 -12/13

2008/09 - 10/11

2009/10 - 11/12

2010/11-12/13

I want to pick  - 2010/11 -12/13

3rd type:

Aug2009 - july 2010

july2010 - sept2011

oct2011- Nov 2012

I want to pick : oct2011- Nov 2012

4th type:

2010

2011

2012

2013

Answer : 2013

Your help is much appreciated !

Hi Daniel,

Many thanks for your response. The problem is i have all the values in same column.Please find the attached data.

Finally i need your help

1. Select latest time period

2. RAG (colour) based on the England values for other areas

Please click the link to see my desired report Public Health Outcomes Framework

Thanks & Regards,

PS

[#2]

Hi there

For types one and four provided the columns are date types you can use MAX(YOURCOLUMNNAME).

For type two and three you might be able to use Peek if the data is loaded in the date order. Ie is it always loaded with the latest date as the last row? If these are summations/groups of your first type then it is better to let QV do the grouping for you.

Alternative is to use makedate and split the ranges into two columns with a start and end date then again use the MAX function.

Good luck!

[#3]

HI again, I would suggest either splitting the columns in excel prior to load if you are new to QV or using the LEFT and RIGHT functions in QV on load to split it into two columns rather than one.

Once loaded you can use visual cues in the chart properties to shade the background colour to match your example...Dan

[#4]

Hi Daniel,

Many thanks for your help,

I have used right function and extracted last 2 digits and use max function to get the latest.

when I created Pivot table and using the max function as shown in the screen shot

1. I am unable to pick only max or latest period

2. How to visual cubes based on the one item values to others i.e. based on England values need to fill other locations that are in the same column

Many thanks

PS

[#5]

Suresh,

When yu have multiple date formats in one single column, you have to use alt() and date#() functions

alt(X, y, Z, ....) returns the first non null argument

date#() interprets a string as a date format (number)

date() formats a number into the wanted format

nested together, you will get:

alt(date(date#([Time Period], 'YYYY - MM'), 'DD/MM/YYYY'),

     date(date#([Time Period], 'YYYY'), 'DD/MM/YYYY'),

    date(date#([Time Period], 'YYYY/MM'), 'DD/MM/YYYY'),

etc.

)

To get the max() use max([Time Period]) as XX ..... group by Domain

To compare with England, I think you will need Set Analysis. May I suggest you to read a doc I have written :

http://community.qlik.com/docs/DOC-4951

You will do sth like : sum(XXX) / sum({set analysis for England} XXX)

or with if() function

Fabrice

[#6]

Hi Fabrice,

Many thanks for your kind help,

I managed to crack the date formats using this script

alt(date(date#([Time Period],'yyyy - yy'),'DD/MM/YYYY') ,

    date(date#([Time Period],'yyyy/mm'),'DD/MM/YYYY'),

    date(date#([Time Period],'yyyy'),'DD/MM/YYYY'),

    date(date#([Time Period],'yyyy/yy - yy/yy'),'DD/MM/YYYY'),

    date(date#([Time Period],'MMM YYYY - MMM YYYY'),'DD/MM/YYYY')

    ) AS RIGHTYEAR,

1. while creating Pivot table, When I am using the Max function under expression, group by its showing error.

thanks

suresh

[Peter_Cammaert]

GROUP BY is a script clause, to be used in a LOAD or SQL SELECT statement. You should pre-calculate the latest period in your script.

A Pivot table does a gorup-by automatically, through the list of dimensions. You can use the max() function without any other additions, but only in an expression. If you want to calculate a latest period-dimension, use aggr(max([NewTimePeriod]), Domain)

Peter

[#8]

Hi Peter,

Many thanks for your response.

Since, I need the same format like i wrote in the following script

alt(date(date#([Time Period],'yyyy - yy'),'YYYY - YY') ,

    date(date#([Time Period],'yyyy/mm'),'DD/MM/YYYY'),

    date(date#([Time Period],'yyyy'),'YYYY'),

    date(date#([Time Period],'yyyy/yy - yy/yy'),'YYYY/YY - YY/YY'),

    date(date#([Time Period],'MMM YYYY - MMM YYYY'),'MMM YYYY - MMM YYYY')

    ) AS RIGHTYEAR,

When I use the aggr(max([RIGHTYEAR]), Domain)

The values are displaying in Number format, but I would like to display as in the above format ( 2010-11 , may 2012 - july 2013 , 2006,2010/11-12/13 etc..)

Thanks a lot to all of you for your invaluable help

Regards,

Suresh

[#9]

date# () interprets a string to get it as a date (number)

date() format this number to the format you want

=> date(date#(xxxx, 'the format you have in the file'), 'the unique format you want in the UI')

and of course, the format you want in the UI is always the same

and the format in the file changes from time to time => hence the alt() function,and many date#() lines

Fabrice

[#10]

Hi there,

I am happy with the Alt(date........) , it producing the formats that  I want.

The problem i got in using max function , if I am using max function, its converting all my time formats to Numbers,

If I am using =Date(MAX(RIGHTYEAR)), if converting all formats to one standard date format

But I want , when I select one Domain, its has many indicators with many time period formats (which need to show at a time)

thank

Suresh