2 Replies Latest reply: Jun 15, 2011 5:20 AM by Ania Domagala RSS

    Set Analysis



      The following formula works below in the expressions:



      avg({<CYear = {"=CYear
       > Year(Today())-10"}, Price = {"if(isnull(Price),0,Price)>0"}, Sale = {"=if(isnull(Sale),0,Sale)>0"},Code={'A','B','C'}>} 1 - (Price/Sale))

      However, the moment I add the following section to the above code, it does not want to produce my results. What am I doing wrong?



      Description = {"Description = ' % Diff - ' & if(MonthID = Month(Today())-1,MonthDescription) & ' ' & (Year(Today())-1)"}


      Thanks in advance for the help.




        • Set Analysis
          Erich Shiino

          Hi, Ania

          What happens when you put this on a char (straight table or pivot) or a text box:

          "Description = ' % Diff - ' & if(MonthID = Month(Today())-1,MonthDescription) & ' ' & (Year(Today())-1)".

          Does it match your fields?


          Can you post some data sample or a sample app so we can check some values and results?





            • Re: Set Analysis

              Hi Erich


              Thanks for the response. Please find attached the file as requested. I have modified the data due to confidentiality reasons.


              You will notice in the table that there is a field called "Total Price to Sale". This shows the summary as per each Description. For 2010 it is 13.12%, for 2011 it is 11.34%.


              I would like to do the same for the graph as reference lines but dynamically, so that as the data changes so do the summary lines for those years. The problem occurs when I add the code from above (original post) with the Description part added to it, then the graph doesn't show that reference line. Without it, it works but it is wrong as it then summaries for both years instead of the one. I need to add two separate reference lines for the two years.


              The string works on it's own but not in the reference line for some reason.