topic Re: Adding 'Start date' and 'End date' to the calendar dynamically in QlikView

Adding 'Start date' and 'End date' to the calendar dynamically

Sat, 16 Jun 2012 07:42:24 GMT

Hi All,

I have a small problem regarding calendar. I have the following fields in the 'Time' table.

I have to add two new columns to the this table in the qlikview script.

They are 'start date' and 'end date'.

'start date' should be the April 1 of the current year and 'end date' should be March 31 of the next year.

eg:

for the column with year key=10 start date='1-April-2010' and end date='31-March-2011'.

We should not hard code for each column,

Please suggest how we can modify the script to get the desired output.

Thanks,

Surendra

Re: Adding 'Start date' and 'End date' to the calendar dynamically

Sat, 16 Jun 2012 10:27:09 GMT

Pls Can any one respond!!

It's a bit urgent!!!

Thanks,

Surendra

Re: Adding 'Start date' and 'End date' to the calendar dynamically

Sat, 16 Jun 2012 11:40:29 GMT

Is this what you're looking for?

load *,
MakeDate((2000+[Year Key]),4,1) as StartDate,
MakeDate(2001+[Year Key],3,31) as EndDate
 Inline
[
Year Key,Calendar Year,Year
10,2010,F11
11,2011,F12
12,2012,F13
13,2013,F14
];

Re: Adding 'Start date' and 'End date' to the calendar dynamically

Sat, 16 Jun 2012 14:37:45 GMT

as Roberto Postma suggest you or

MakeDate([Calendar Year],4,1) as StartDate,

MakeDate(([Calendar Year]+1),4,1)as EndDate

Re: Adding 'Start date' and 'End date' to the calendar dynamically

Mon, 18 Jun 2012 07:28:29 GMT

Thanks a lot Roberto and Mohammed Own for your help.

Sorry for extending this discussion. I have an issue regarding this.

I have to display a default Year. The default Year is displayed in such a way that today's date is checked to find out under which start date and end date it falls and that year is displayed as default Year.

eg: Today's date is 18/06/2012 and it falls between the range 01/04/2012 to 31/03/2013. Hence the default year to be displayed is F13.

Can you help me regarding the same.

Re: Adding 'Start date' and 'End date' to the calendar dynamically

Mon, 18 Jun 2012 07:33:23 GMT

Hi Surenda-m,

How do you have to display that?

You mean in a chart or something?

Could you post a sample-qvw with an indication where your problem lies? Then lets see how I can help...

For me it is now unclear if your issue is still in the load-script, or if you want to have some "initial selection" based on the current year; I hope you can elaborate a bit.

Re: Adding 'Start date' and 'End date' to the calendar dynamically

Mon, 18 Jun 2012 07:49:02 GMT

Hi Roberto,

I have to display the default year value in a list box. I feel it can be done in the front end.

I am unable to find the upload of qvw option in the discussion page.

Thanks,

Surendra

Re: Adding 'Start date' and 'End date' to the calendar dynamically

Mon, 18 Jun 2012 07:51:35 GMT

I think this should be helpful for you then. Could you let me know if that helped?

http://community.qlik.com/thread/27836

Re: Adding 'Start date' and 'End date' to the calendar dynamically

Mon, 18 Jun 2012 07:57:37 GMT

No Roberto. That's not what I was looking for.

Let's consider this example.

Today's date is 18/06/2012 . The default Year that is to be displayed is F13.

If today's date is 01/01/2013 then the default Year that is to be displayed is F13.

I think we have to check under which start date and end date range today's date falls under and we have to display the Year(Financial) accordingly.

Thanks,

Surendra

Re: Adding 'Start date' and 'End date' to the calendar dynamically

Mon, 18 Jun 2012 09:01:11 GMT

Another try... If that is not what you're looking for, perhaps some of the others can try to help 😛

- Find the attached qvw

- note that when the variable vTestDateToday is set to a new (test)value AND you switch between sheet1 and Main... the (as far as I understand your problem correctly) right selection is set...

- The reason is that I defined (at sheet level) a trigger to do the selection and applied some setAnalysis to figure out which year to take

So what I wonder is:

[1] does it pick the right year for every inputdate that you test with? (if not, what is my misunderstanding)

[2] does this fix your problem?

Hope it helps 🙂

Re: Adding 'Start date' and 'End date' to the calendar dynamically

Mon, 18 Jun 2012 12:16:36 GMT

Wonderful Roberto!!

Thanks a ton!!

=only(
{
1<
//StartDate={'<=$(=time(now()))'},
//EndDate={'>=$(=time(now()))'}
StartDate={"<=$(=date(today()))"},
EndDate={">=$(=date(today()))"}

>}
Year)

That's what I was looking for.

Roberto,

Can you let me know when only() function is used!!

might sound a bit silly to you:)

I'm very new to QlikView and i'm in the process of learning Set Analysis.

Surendra

Re: Adding 'Start date' and 'End date' to the calendar dynamically

Mon, 18 Jun 2012 12:24:52 GMT

Hi Surendra-m,

You're welcome.

About the only-function:

This is what the reference manual says about it:

only(expression )
If expression over a number of records, as defined by a group by clause, contains only one numeric value,
that value is returned. Else, NULL is returned.

In my own words

- I use it when the outcome must be exactly one value (if it is more, Null() is returned, which is usually not useful)

- It is an aggregation-function, so you can apply setAnalysis in this function

- I use it for a case like this, where the "logic" is such that when the filters are applied correctly, there is only one record / value to return

For setAnalysis there are good references online; this one I like most of them; I'll attach it here.

If you would have questions on SetAnalysis, I suggest you open a new topic, since that would be offtopic for this one 🙂

Happy learning!