11 Replies Latest reply: May 11, 2009 5:20 PM by adjames RSS

    Selected value of a list box YTD comparison


      A YTD Comparison question.. Currently I'm loading our fiscal dates and fiscal year into a table. The fiscal year is located in a list box that allows the end user to select the fiscal year they wish to view. The pivot table has a 2008 column and 2009 column respectively. However, this is a static method where I load code into a 2008 field if it is a specific date range.


      What I want to do is make it dynamic. If a user uses the list box and selects fiscal year 2009 I want to automatically know that the previous year is [Current year selected in list box] - 1.


      Not sure how to go about this or pulling the (current selected value) from a list box.

      Any thoughts or pointers in the right direction would be much appreciated.





        • Selected value of a list box YTD comparison
          Shima Auzins

          I think you are looking for a function called, GetFieldSelections. So the syntax is GetFieldSelections (FiscalYear) which returns 2009, so -1 of that is 2008 dynamically.

          But probably the best way is to use set analysis if you are using 8.5+.

          Max function:

          For Current Year = Sum({$<Year={$(=Max(Year))}>} Sales)

          For Last Year = Sum({$<Year={$(=Max(Year)-1)}>} Sales)


          For Current Year = Sum({$<Year={$(=GetFieldSelections(Year))}>} Sales)

          For Last Year = Sum({$<Year={$(=GetFieldSelections(Year)-1)}>} Sales)

          I hope this helps.

            • Selected value of a list box YTD comparison

              This helps a lot and I appreciate your reply. In regards to set analysis I've tried that. One thing I notice is it pretty much breaks the "show partial sums" total rows when used.

              Do you know why this is or if there is a possible method around it?


              Thanks for your input.



                • Selected value of a list box YTD comparison
                  Johan Hessler

                  Have a look at the RowNo() function. You can, with the RowNo() have a different expression for your partial sums. I dont know for sure (havn't axx to Qv atm) but i think it is something like this;


                  if(RowNo()=0,<expression for partial sum>,<ordinary expression>)

                    • Selected value of a list box YTD comparison

                      Here's another idea.

                      If you have a date field in the data (I'm assuming you have a true date field or a year field), then you can avoid set analysis. I'll call that field Year. I'll also call Cost the field you want t calculate.

                      Here's what you do -

                      Create a variable in an input box called Year_Var. Year_Var has to have Enable Edit Expressions turned off, set to values in a predefined list, force a list of values 2009;2008;2007;2006 etc... and set your default value to 2009.

                      Next, create a chart and set the enable condition on the table to tell the customer to select at least two distinct years (e.g you can do this with show condition of count(distinct Year) > 1. You can set up a calculation message as well with this.

                      In the chart, select a straight table and pivot table. Select the dimensions you want. Don't add in dates to the dimensions.

                      In the expressions tab, add three expressions:


                      Expression 1 => sum


                      (if(Year_Var=Year,Cost,0)) For the title => =Year_Var


                      Expression 2 => sum(if(Year_Var-1=Year,Cost,0)) For the title => =Year_Var -1

                      Expression 3 => sum(if(Year_Var=Year,Cost,0)) - sum(if(Year_Var-1=Year,Cost,0)) For the title => Delta

                      This should be all you need to do. Hopefully this will do everything you want while and will probably be more efficient than set analysis. It also gives you more flexibility to create year-to-date analysis utilizing if-then logic.

                • Selected value of a list box YTD comparison
                  Rob Wunderlich

                  One approach is to copy the Year values into a disconnected "date island" field.

                  LOAD Year as Selected_Year RESIDENT data;

                  Put Selected_Year in a listbox with "always one selected".

                  For your charts, use a calculated dimension like:
                  =if(match(Selected_Year - Year, 0, 1), Year)

                  Eample attached.



                    • Selected value of a list box YTD comparison

                      Rob, I like your reply. However, I'm trying to implement this in the column area where the dimensions do not have the date but the expression is based off the selected date and the previous year.


                        • Selected value of a list box YTD comparison

                          I think that if you want to force the user to have just one year selected and you want to compare it to a previous year of data, then you need to use set analysis.

                          If you don't care if they have multiple years selected, then you can use Rob's suggestion of a year with a list based off of your load data (which I happen to like as it's dynamic each time you reload) or utilizing a variable with fixed parameters.

                          • Selected value of a list box YTD comparison
                            Rob Wunderlich

                            You can still use this as an expression even if Year is not a dimension in the chart. Like if you were trying to sum the sales for the selected year and it's prior year.

                            =Sum(if(match(Selected_Year - Year, 0, 1), Sales))

                            or Sales for the just the prior year.

                            =Sum(if(Selected_Year - Year = 1, Sales))



                              • Selected value of a list box YTD comparison

                                Rob, Thanks again for your help/replies and to everyone else for coming up with such uniqe ideas.

                                I'm currently trying to implement your last suggestion and I can't get the previous year/current year to display at the same time. Here is my screen shot and code. Any input would be appreciated.

                                And looking at the code i can agree that it wont show previous and current at the same time depending on year selected. But have been unsucessful when trying to find an alternative.







                                (match(Selected_year - Year, 1), sum([Invoiced]






                                (match(Selected_year - Year, 0), sum([Invoiced]))




                                  • Selected value of a list box YTD comparison
                                    Rob Wunderlich

                                    Your screen shot shows that 2009 is selected for "Year" as well. That means that there is no data selected for 2008. If you are also going to have Year selected, you'll have to use Set Analysis or the ALL keyword to be able to access the unselected year data. For example.

                                    if(match(Selected_year - Year, 1), sum( ALL [Invoiced] ))

                                    If you are going this route, you may find that you don't need the SelectedYear field and can just use Year.


                                      • Selected value of a list box YTD comparison

                                        Normal 0

                                        Rob, Thanks for all your replies.


                                        This works. However, what if nothing is selected? I have three "select_year" 2007 2008 and 2009 which also means I have three years. How can I add the if statement that says "

                                        if(match(Selected_year - Year, 1), sum( ALL [Invoiced] )) else, sum where year = today() etc.


                                        So it displays the current year previous year and previous year -2.