topic Re: Date Selections in QlikView

Date Selections

sunilkumarqv — Mon, 17 Feb 2014 09:53:33 GMT

I have a dates like 1/31/2010,1/31/2011,1/31/2012,1/31/2013 (M/DD/YYY)

.I created chart above date as dimension ,So here if selected date 1/31/2012 in chart showing only 1/31/2012 I pervious dates will be diappered . I want to be presented previous dates as well I will appricate any one heelp me.

Re: Date Selections

MK_QSL — Mon, 17 Feb 2014 10:00:10 GMT

Can you upload your sample apps?

Update :

You can use like below

SUM({$<Date = >}Sales)

Re: Date Selections

Mon, 17 Feb 2014 10:04:15 GMT

Hi,

If you are using any aggregated funciton in your chart expression then try to use {<Datefield=>} in SET expression.

Re: Date Selections

sunilkumarqv — Mon, 17 Feb 2014 10:15:19 GMT

Hi Manish

This sceniro works only actual data in database but some dates are dynamically created what to do so.

Re: Date Selections

MK_QSL — Mon, 17 Feb 2014 10:22:41 GMT

Better if you share your sample file !

Would love to help you.

Re: Date Selections

qlikvfpi — Mon, 17 Feb 2014 10:38:54 GMT

Try this :

sum( {$<Date={'<=$(=Date)'}>}...)

Re: Re: Date Selections

sunilkumarqv — Mon, 17 Feb 2014 10:52:31 GMT

Please see below screen shot .

i used below script for dynamically created dates and want to be create DateId for this so i can use in my experssion as u said.

or any other idea?

set DateFromat='M/D/YYYY';

set vMaxdate=date(today());

Let vStart = date('12/31/2012');

Let vEnd = date('12/31/2014');

LOAD MonthEnd(date) as MonthEndDate;

LOAD Date('$(vStart)' + RecNo()) as MonthEndDate autogenerate '$(vEnd)' - '$(vStart)' ;

Re: Date Selections

MK_QSL — Mon, 17 Feb 2014 11:07:55 GMT

You can create something like below...

Let vStart = NUM(Date('31/12/2012'));

Let vEnd = NUM(Date('31/12/2014'));

TempDate:

Load

Date($(vStart) + RowNo() - 1) as Temp_Date

AutoGenerate $(vEnd) - $(vStart)+1;

Load AutoNumber(Temp_Date) as DateID,

Temp_Date as Date

Resident TempDate

Order By Temp_Date;

Drop Table TempDate

Re: Date Selections

sunilkumarqv — Mon, 17 Feb 2014 11:12:45 GMT

Getting Script error

Re: Date Selections

MayilVahanan — Mon, 17 Feb 2014 11:17:07 GMT

SET DateFormat='DD/MM/YYYY';

Let vStart = NUM(Date('31/12/2012'));

Let vEnd = NUM(Date('31/12/2014'));

TempDate:

Load

Date($(vStart) + RowNo() - 1) as Temp_Date

AutoGenerate $(vEnd) - $(vStart)+1;

Load AutoNumber(Temp_Date) as DateID,

Temp_Date as Date

Resident TempDate

Order By Temp_Date;

Drop Table TempDate;

Re: Date Selections

sunilkumarqv — Mon, 17 Feb 2014 11:45:16 GMT

Is possible show values in chart for dynamically created dates

Re: Date Selections

MK_QSL — Mon, 17 Feb 2014 12:16:58 GMT

Can you elaborate your requirement in little depth?

Re: Date Selections

sunilkumarqv — Mon, 17 Feb 2014 12:27:07 GMT

I have only 3months of data with some value .

actually that 3months data is like 1/31/11,1/31/12,1/3/13 as Monthyear

in qlikview chart i taken date as dimesion and some exps , So here in chart only comes that data. If we select 1/31/12

it want to display month end dates for example like this 1/31/11,2/31/11,3/31/11,4/31/11,5/31/11....12/31/11 dates in

same way if we select 1/3/13 it should display 1/31/11 to 1/3/13

is there any way to do that .

Re: Date Selections

MK_QSL — Mon, 17 Feb 2014 12:41:01 GMT

Let vStart = NUM(Date('31/12/2012'));

Let vEnd = NUM(Date('31/12/2014'));

TempDate:

Load

Date($(vStart) + RowNo() - 1) as Temp_Date

AutoGenerate $(vEnd) - $(vStart)+1;

Load

AutoNumber(Date,'Date') as DateID,

AutoNumber(YearMonth,'YearMonth') as YearMonthID;

Load

Temp_Date as Date,

Month(Temp_Date) as Month,

Year(Temp_Date) as Year,

Year(Temp_Date)&Month(Temp_Date) as YearMonth

Resident TempDate

Order By Temp_Date;

Drop Table TempDate;

========================================

Now You can use something like below...

Create a List Box for YearMonthID

SUM({<YearMonthID = {'$(=Max(YearMonthID)-12)'}>}Sales)

This will give you Last 12 Months Sale..

You can obviously change 12 with your required number.

Re: Date Selections

sunilkumarqv — Mon, 17 Feb 2014 13:07:57 GMT

no its not working any other way do that

Re: Date Selections

MK_QSL — Mon, 17 Feb 2014 13:23:45 GMT

It will be easy to work if you update you sample apps.

Re: Date Selections

sunilkumarqv — Mon, 17 Feb 2014 13:40:01 GMT

see this i littel tired its realy helpful to me