5 Replies Latest reply: Mar 11, 2015 9:26 AM by Andreas Felderhoff RSS

    Sales Previous Year

    Andreas Felderhoff

       

      I’m searching for a rule to get the Sales (red text) of the previous Year for a selected timeline

       

      (example: Feb 14 – Mrz15). Has anybody an idea?

       

       

       

      Calendar

       

       

       

       

       

       

       

       

       

       

      Jan 13

      Feb 13

      Mrz 13

      Apr 13

      Mai 13

      Jun 13

      Jul 13

      Aug 13

      Sep 13

      Okt 13

      Nov 13

      Dez 13

      Jan 14

      Feb 14

      Mrz 14

      Apr 14

      Mai 14

      Jun 14

      Jul 14

      Aug 14

      Sep 14

      Okt 14

      Nov 14

      Dez 14

      Jan 15

      Feb 15

      Mrz 15

       

       

       

       

       

       

       

       

       

       

       

       

      SalesSales Prev.Y.
      Feb 14Feb 13
      Mrz 15Mrz 14
      Total1.500.0001.400.000
        • Re: Sales Previous Year
          Gysbert Wassenaar

          Usually one would use a date field for this calculation. Suppose you have Month-Year field like in your screenshot and a Date field. You can use a set analysis expression like these:

           

          Current year: sum({<[Month-Year]=, Date={">$(=addyears(max(Date),-2))<=$(=addyears(max(Date),-1))"}>}Sales)

          Previous year: sum({<[Month-Year]=, Date={">$(=addyears(max(Date),-1))<=$(=max(Date))"}>}Sales)

            • Re: Sales Previous Year
              Andreas Felderhoff

              Thank you, but it didn’t works. I've got only sales, if I use the variable. But I get much more sales as the selected months. This is the part of the script about the Date and the set analysis:

               

               

              Script:

               

              Date (VPZDTT&'.'&VPZDMM&'.'&VPZDJJ) As DATUM,
              Year (VPZDTT&'.'&VPZDMM&'.'&VPZDJJ) as JAHR,
              Month (VPZDTT&'.'&VPZDMM&'.'&VPZDJJ) as MONAT,
              Day (VPZDTT&'.'&VPZDMM&'.'&VPZDJJ) as TAG,
              Date (monthstart(VPZDTT&'.'&VPZDMM&'.'&VPZDJJ), 'MMM.YYYY') as MONATJAHR,

               

               

              Variable:

               

              MinDatum =AddYears(min(MONATJAHR),-1)

               

              MaxDatum =AddYears(max(MONATJAHR),-1)

               

               

               

               

              =sum({<[MONATJAHR]=, DATUM={">$(MinDatum))<$(MaxDatum)"}>}VPUMNH)

               

            • Re: Sales Previous Year
              bobbyraj santhiogu

              Hi,

              You can use a set analyses.

              You must say use an expression to set:

              date<addyear(max(date),-1) AND date >addyear(min(date),-1)

              It should be like:

              sum({$<Date = {“>$(=addyear(min(Date),-1)<$(=addyear(max(Date),-1)”}>} sales)

              • Re: Sales Previous Year
                Andreas Felderhoff

                Thank you very much.

                I modified it a little bit and now it works.

                Here the solution for all others:

                 

                 

                Script:

                Date (VPZDTT&'.'&VPZDMM&'.'&VPZDJJ) As DATUM,
                Year (VPZDTT&'.'&VPZDMM&'.'&VPZDJJ) as JAHR,
                Month (VPZDTT&'.'&VPZDMM&'.'&VPZDJJ) as MONAT,
                Day (VPZDTT&'.'&VPZDMM&'.'&VPZDJJ) as TAG,
                Date (monthstart(VPZDTT&'.'&VPZDMM&'.'&VPZDJJ), 'MMM.YYYY') as MONATJAHR,

                 

                 

                Variable:

                MinDatum=AddYears(min(MONATJAHR),-1)

                MaxDatum=AddMonths((AddYears(max(MONATJAHR),-1)),1)

                 

                =sum({<[MONATJAHR]=, DATUM={">$(MinDatum)<$(=MaxDatum)"}>}Sales)