topic Re: How to customize the date in QlikView

How to customize the date

Fri, 04 May 2012 13:08:17 GMT

I have attached a qv application. It will have data received as a date column and i have below query to get year, day and month.

year([Date Received]) as year,

day([Date Received]) as day

,Month([Date Received]) AS Month

Now the main thing i need is a new field month which should satisfy the below thing , whenver i include month as a list box it should show the jan month to current month.

Examples:

forFebruary 2012 you would pull January 2012 and February 2012 YTD info

forMarch 2012 you would pull Jan 2012, Feb 2012 and Mar 2012 YTD info

Re: How to customize the date

Fri, 04 May 2012 18:01:11 GMT

I think this might be what you are aiming for...

First we need to define some variables:

add variable months and define it as ='Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec'

add variable monthSelected and define it as =GetFieldSelections(Month)

add varaible selected and define it as = trim( left(months,(index(months, monthSelected)+2)))

add variable temp2 and define it as = '('&Replace(selected, ' ', '?')&')'

Now we just need to add a trigger to the document (settings->document properties->triggers tab)

Field Event Triggers -> Month -> Add Actions for OnSelect

Add -> Select in Field -> Field = Month and Search String = =temp2

Now when you make a selection for Month it will pick everything before it for the year as well.

Here is an attachment for reference.

Hope this helps!

-Brandon

Re: How to customize the date

danielact — Fri, 04 May 2012 18:40:32 GMT

In the script, set up another table with a list of the months. I find the easiest way is to do an inline load with dates from each month (just use 1/1/12, 2/1/12, etc.), then do a month function on it. Name the field a different name - let's use SelectMonth. The statement should look like this:

SelectMonth:

LOAD Month(F1) as SelectMonth INLINE [

1/1/2012

2/1/2012

3/1/2012

4/1/2012

5/1/2012

6/1/2012

7/1/2012

8/1/2012

9/1/2012

10/1/2012

11/1/2012

12/1/2012

];

Instead of a Table Box, use a Straight Table. Then create a dimension with the following:

if(Month<=month(SelectMonth),1,null())

Set this dimension to suppress when value is null. You should also then hide this column (on the Presentation tab).

If you don't have any expressions, just put 1 into an expression, then hide it (again, on the Presentation tab).

How to customize the date

Mon, 07 May 2012 07:38:32 GMT

Thanks Brandon,

Your solution was very good. I need one more advanced of it , that when i clear the selection , the month field is getting cleared and it is displaying all the moths. The month field should always display only the completed months of the year and not the other months.

Regards,

Gerard

How to customize the date

Mon, 07 May 2012 10:21:53 GMT

The month field should contain only the completed month list of current year( even the year may be 2010,2011) and remaining month should not be shown. How to create such month field using variable.

How to customize the date

Wed, 09 May 2012 06:00:03 GMT

Hi Brandon,

Is there any way to approch this. Please provide me some suggestion.

How to customize the date

Wed, 09 May 2012 17:15:28 GMT

Sorry for the delay I've been a little under the weather.

I am not quite sure what the problem is. I know you are saying that you only want the completed months to be shown, and when you select the year 2012, it grays out Jun-Dec, indicating that there is no data for Jun - Dec 2012 yet. Do you want it to be the case where it grays out Jun-Dec and 2012 isn't selected?

Maybe try to give an example if I am still not understanding this.

Thanks,

Brandon

Re: How to customize the date

Thu, 10 May 2012 06:09:01 GMT

Happy to see you back,

The scenario is , earlier in the script we are having month field and year field. i want one more month field for example it should show only completed months of 2012. If we pull that field as a list box( Month2) it should show only (Jan,Feb,March, April) . From tat we can select any of the month we wish. This applies for other year too (2008,2009,2010,2011) it should also show the completed months of 2012.

If i pull the month field it should be like this. Even if i select any of the year , the month field should show only the completed month of 2012.From this i can able to select any combination.

Regards,

Gerard

Re: How to customize the date

Thu, 10 May 2012 17:11:55 GMT

Okay, I think I may have implemented it that way you are wanting it to be done... Let me know what you think.

This just does a check to see if data exists for a particular month on the month end. If it does then it includes the month, then rejoins to the original table with just those months so all years have just the months completed in 2012. I also added back in the macro where if you select Feb, it selects Jan and Feb, etc...

Hope this helps!

-Brandon

Re: How to customize the date

Tue, 15 May 2012 08:10:24 GMT

Thanks Brandon , for this application it is working fine. When i try the same concept to my original application(Connected to SQL datadase) it is not working fine, when i drop the table after join, the new table is not having any value, it showing null. Is there any other way to achieve it, without join concept. Is there a way we can do this via variable.

Regards,

Gerard

Re: How to customize the date

Tue, 15 May 2012 12:35:52 GMT

When i implemented your logic in my application , table 2 is showing - instead of values.Even after doing the left join with table 1 , table 2 is not having values. Can you suggest anyother method to get the completed months, either it can be in front end or in script.

Re: How to customize the date

Tue, 15 May 2012 16:08:11 GMT

Can you attach a screenshot of what's going wrong in the final display. Sorry, I'm having difficulty understanding what problem is occuring.

Thanks,

Brandon

Re: How to customize the date

Wed, 16 May 2012 13:21:47 GMT

Hi Brandon,

When you check the table view for table2 , field Month2 is swowing - .But if you pul it as a field in front end we can see the completed months.

And please explain the concept of this query , since with this single query you are getting the completed months of 2012 in month2.

IF(year([Date Received]) = '2012' and day(monthend([Date Received])),text(Month([Date Received]))) as Month2

Re: How to customize the date

Fri, 18 May 2012 05:56:24 GMT

Hi Brandon,

Can you please help me out in the earlier application. You made it work like the way i said. Can you help me out for the scenario, when i select the year as 2008 or 09 or 10 or 11. It should display all the month (completed months of that year) but when i select 2012 it should show only the completed month.

Regards,

Gerard

Re: How to customize the date

Fri, 18 May 2012 15:51:57 GMT

The idea behind this:

IF(year([Date Received]) = '2012' and day(monthend([Date Received])),text(Month([Date Received]))) as Month2

Is if data exists for 2012 and the last day of a particular month, then you know that month has been completed so you want to compile your list of months. (Of course this might break if you have completed a month but you do not have data on the last day of the month, I was assuming you did). This stores just Jan, Feb, Mar etc... of the completed months into Month2.

Then the idea is to left join that Month2 field to the original month field. That is everything that exists in Month2 that matches Month, but excludes the months that aren't completed.

What has been happening now that needs repairing? Maybe if you attach the application I can try and assist you better.

Thanks

Re: How to customize the date

Mon, 28 May 2012 13:27:23 GMT

Hi Brandan,

Need a help, even if there is data for all months of 2012. I want to show only till the current month. The month field for 2012 should be till may(current month) , the other month should not be visible.This is for both list box and multi box.