21 Replies Latest reply: Jan 18, 2011 8:08 AM by At titude RSS

    Hard coded year to dynamic year

      Hi

      Please make the hard coded year in the expression in to dynamic(Year as the selection). I know it is very simple to do so but it is not working.

      i,e YEAR is hardcode here, I want to make it as Max(YEAR ). So that by default it will show the current year and if multiple year are selected it will show MAX(YEAR ) in selected.

      Expression:

       


      =sum(if(YEAR='2011' and wildmatch(Category,'*Call*') > 0 and (wildmatch([Agreement Met],'no')>0),1,0)) /
      sum(if(YEAR='2011' and wildmatch(Category,'*Call*') > 0 ,1,0))


        • Hard coded year to dynamic year
          Jonathan Dienst

          Hi

          Just replace the '2011' with one of:

          Max(YEAR) - to respect chart dimensions and selections

          Max(Total YEAR) - to respect selections that affect YEAR, but ignoring chart dimensions (This is probably what you want)

          Max({YEAR=} Total YEAR) - ignore selections on year and chart dimensions
          Max({YEAR=,MONTH=,DAY=} Total YEAR) - ignore selections on YEAR, MONTH or DAY

          I often use the second. It has the advantage that if the user makes no selection, it contains the current year. If the user DOES make a selection that affects YEAR, it will contain the YEAR for the selection (or the maximum value of YEAR allowed by the selections if more than one year).

          Hope that helps

          Jonathan

            • Hard coded year to dynamic year

               


              Jonathan Dienst wrote:
              Hi
              Just replace the '2011' with one of:
              Max(YEAR) - to respect chart dimensions and selections
              Max(Total YEAR) - to respect selections that affect YEAR, but ignoring chart dimensions (This is probably what you want)
              Max({YEAR=} Total YEAR) - ignore selections on year and chart dimensions
              Max({YEAR=,MONTH=,DAY=} Total YEAR) - ignore selections on YEAR, MONTH or DAY
              I often use the second. It has the advantage that if the user makes no selection, it contains the current year. If the user DOES make a selection that affects YEAR, it will contain the YEAR for the selection (or the maximum value of YEAR allowed by the selections if more than one year).
              Hope that helps
              Jonathan<div></div>


              I tried both 1st and 2nd but it didn't seems to be working. Can you please do the required changes in the expression please!

               


              =sum(if(FINISH_YEAR='Max(Total YEAR)' and wildmatch(Category,'*Call*') > 0 and (wildmatch([Agreement Met],'no')>0),1,0)) / sum(if(FINISH_YEAR='Max(Total YEAR)' and wildmatch(Category,'*Call*') > 0 ,1,0))


               

                • Hard coded year to dynamic year
                  Jonathan Dienst

                  Hi

                  Take off the quote marks from the expression.

                   

                  sum(if(FINISH_YEAR=Max(Total YEAR) and wildmatch(Category,'*Call*') > 0 and (wildmatch([Agreement Met],'no')>0),1,0)) /
                  sum(if(FINISH_YEAR=Max(Total YEAR) and wildmatch(Category,'*Call*') > 0 ,1,0))



                  If that still does not work, test with this to make sure that the problem is not somewhere else

                  sum(if(FINISH_YEAR=Max(Total YEAR), 1, 0))



                  You could also check the possible values for FINISH_YEAR (make a list box), and check that Max(Total YEAR) is giving you the value you expect (make a text box with =Max(Total YEAR) as the text)

                  Jonathan

                    • Hard coded year to dynamic year

                       


                      Jonathan Dienst wrote:
                      Hi
                      Take off the quote marks from the expression. <blockquote>sum(if(FINISH_YEAR=Max(Total YEAR) and wildmatch(Category,'*Call*') > 0 and (wildmatch([Agreement Met],'no')>0),1,0)) /
                      sum(if(FINISH_YEAR=Max(Total YEAR) and wildmatch(Category,'*Call*') > 0 ,1,0)) </blockquote>
                      If that still does not work, test with this to make sure that the problem is not somewhere else<blockquote>sum(if(FINISH_YEAR=Max(Total YEAR), 1, 0))

                      You could also check the possible values for FINISH_YEAR (make a list box), and check that Max(Total YEAR) is giving you the value you expect (make a text box with =Max(Total YEAR) as the text) </blockquote><div></div>


                      It seems to work for the Max(Year) but when I use for the Max(Year)-1 it doesn't seems to work. Could you please check and let me know why it is not working.

                       

                      <blockquote><pre>sum(if(FINISH_YEAR=Max(Total YEAR)-1 and wildmatch(Category,'*Call*') > 0 and (wildmatch([Agreement Met],'no')>0),1,0)) /
                      sum(if(FINISH_YEAR=Max(Total YEAR)-1 and wildmatch(Category,'*Call*') > 0 ,1,0))

                       

                       

                       

                       

                      • Hard coded year to dynamic year

                        Hi Jonathan,

                        I think once I select the dimension(Year) it does seems to be working properly. Max(Year) seems to be fine but Max(Year)-1 doesn't look good.

                  • Hard coded year to dynamic year
                    Anatoly Pyatygo

                    Hello,

                    At first, I think that you should use Set Analysis, instead of IF statement into Sum() function...

                    and about your question...

                    try this:

                    sum(if(YEAR=$(=max(YEAR)) and...))

                     

                      • Hard coded year to dynamic year

                         


                        sparur wrote:
                        At first, I think that you should use Set Analysis, instead of IF statement into Sum() function...
                        and about your question...
                        try this:
                        sum(if(YEAR=$(=max(YEAR)) and...))


                        As per your suggestion I exactly did the same thing but it didn't seems to be working. Please check and let me know what may be wrong with the expression. After using this expression I am getting "No data to display"

                         


                        =sum(if(YEAR='$(=max(YEAR)' and wildmatch(Category,'*Call*') > 0 and (wildmatch([Agreement Met],'no')>0),1,0)) / sum(if(YEAR='$(=max(YEAR)' and wildmatch(Category,'*Call*') > 0 ,1,0))


                      • Hard coded year to dynamic year
                        s j

                        =sum(if(Stock_Date=MaxStockDate,Stock_Value))

                        MaxStockDate is varable that contain

                        MaxStockDate = Max(BIll_Date)

                          • Hard coded year to dynamic year

                             


                            Sunil Jain wrote:
                            =sum(if(Stock_Date=MaxStockDate,Stock_Value))
                            MaxStockDate is varable that contain
                            MaxStockDate = Max(BIll_Date)<div></div>


                             

                            Sorry I tried it but didn't seems to be working.

                             


                            =sum(if(YEAR=MaxYear and wildmatch(Category,'*Call*') > 0 and (wildmatch([Agreement Met],'no')>0),1,0)) / sum(if(YEAR=MaxYear and wildmatch(Category,'*Call*') > 0 ,1,0))


                            MaxYear is varable that contain

                            MaxYear = Max(FINISH_YEAR)

                            PrvYear is varable that contain

                            Prv = Max(FINISH_YEAR)-1