15 Replies Latest reply: May 28, 2012 9:27 AM by Gerard Carvalho RSS

    How to customize the date

      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.






      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

          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!



          • Re: How to customize the date
            Daniel Fleisher

            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:



            LOAD Month(F1) as SelectMonth INLINE [

















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



            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

              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.




                • Re: How to customize the date

                  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.




                    • Re: How to customize the date

                      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!


                        • Re: How to customize the date

                          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.




                          • Re: How to customize the date

                            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

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




                                  • Re: How to customize the date

                                    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

                                        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.