2 Replies Latest reply: Dec 13, 2011 9:41 AM by Torben Seebach RSS

    How to use the dimension value in an expression (chart)

      Hi all,

       

      I have been trying to contruct a chart, which has a target value based on a trend from the previous year. It is used to see how much sales mtd in the current month there are supposed to be compared to the same month last year. It works fine, when you calculate one number, but when I put it in a graph, which is accumulating, I get the same value for each day in the month. I have been told to use the above and range funtions, but haven't been able to figure out how to implement it. Any help is highly appreciated.

       

      Below is the expression, which needs to take the dimension value into account:

       

      (
      sum({$<[Date]={">=$(=(Floor(MonthStart(AddYears(Max( [Date]),-1)))))"}*{"<=$(=(Floor(AddYears(Max(Today()),-1))))"},[Calender MonthName]=,[Calender Year]=>}DB)
      /
      sum({$<[Date]={">=$(=(Floor(MonthStart(AddYears(Max( [Date]),-1)))))"}*{"<=$(=(Floor(MonthEnd(AddYears(Max(Today()),-1)))))"},[Calender MonthName]=,[Calender Year]=>}DB)
      )
      * 
      Sum({$<[Date]={">=$(=(Floor(MonthStart(Max( [Date])))))"}*{"<=$(=(Floor(MonthEnd(Max( [Date])))))"},[Calender MonthName]=,[Calender Year]=>} BudgetDB)
      )
      
      

       

      The first two lines calculate the trend from the month last year, the last is the budgeted sales for the current month. So on the e.g. the 10th in the current month you get a value on how much of your budget you should have accomplished. And I want to put this in an accumulating chart.

       

      Thanks in advance!