How to customize the date

[#1]

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

[#2]

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

[danielact]

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 [

    F1

    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).

[#4]

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

[#5]

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.

[#6]

Hi Brandon,

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

[#7]

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

[#8]

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

[#9 · Accepted solution]

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

[#10]

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