4 Replies Latest reply: Oct 8, 2012 1:28 PM by lgeller83 RSS

    Selected Month vs Previous Month

      I am trying create an expression which calculated the difference between the selected month's costs and the one preceeding it.

       

      Below is an explanation of my key fields:

      • [Charge Rate] - Column with spend I am trying to sum
      • [Data Feed Date] - Date formatted as 8/1/2012 12:00 AM
      • [Data Feed Month] - Date formatted as Aug 2012

       

      I do not want to be tied to the most recent month (I want to be able to select which month I am viewing). I was able to create a label using the following formula (=AddMonths([Data Feed Month],-1)).

       

      I tried applying this to my expression but it is not working. (sum({<[Data Feed Month]={'=AddMonths([Data Feed Month],-1)'}>}[Charge Rate]))

       

      Any help would be greatly appreciated.

        • Re: Selected Month vs Previous Month
          Nate Hallquist

          Have you tried using the function "GetCurrentSelection" or "GetCurrentField"?  I think you need to use GetCurrentSelection, on this. 

           

          Give it a try and let me know...

           

          Nate

            • Re: Selected Month vs Previous Month

              Getcurrentselection appears to list in words everything that is currently selected (like a current selection box). The problem is this lists everything and I am only concerned about what date I am filtered on and displaying the previous month.

                • Re: Selected Month vs Previous Month
                  Nate Hallquist

                  Take a look at GetFieldSelections.  That might be what you are looking for. 

                   

                  I also use this formula...

                   

                  (Sum({$< BookedYear={$(=Max(BookedYear)-1)}>}  (ORDERED_QUANTITY*UNIT_SELLING_PRICE)))

                   

                  In this case, you have to force the user to select a year, but you can do that by using the Conditional Expression so that the expression won't calculate unless a dimension value is chosen.   I avoided that by setting this tab to select Current year when activated. 

                   

                  Will this suffice?

              • Re: Selected Month vs Previous Month

                I found the solution from another discussion thread. It appears I needed to format as a date for it to work.

                 

                Below is the solution and the explanation from the other thread

                 

                (Sum({<[Data Feed Month]={"$(=date(AddMonths([Data Feed Month],0),'MMM YYYY'))"}>} [Charge Rate]))

                 

                You need to format the SelDate after you subtract the month. It doesn't preserve your special formatting, and is doing more of a text comparison than a numeric comparison. Also, once you do format it, you're going to have to put it in quotes or QV will probably get confused. So I'm guessing that this would work:

                Sum({1<SelDate={"$(=date(AddMonths(SelDate,-1),'MMM YYYY'))"}>} DollarAmount)