11 Replies Latest reply: May 4, 2017 4:06 AM by Guy Hombleu RSS

    YTD vs Last YTD Comparison

    Guy Hombleu

      Hi Guys,

       

      Could someone help me out a bit. I've already used the advice from topic: Year to Date vs Year to date Last Year

       

      But for some reason my calculation doesnt works for the prioryear.

      I added an image where you can see the variables and calculations.

       

      Thnx!

        • Re: YTD vs Last YTD Comparison
          omar bensalem

          Please refer to this thread :

          YTD, MTD issue

          • Re: YTD vs Last YTD Comparison
            kvp kumar

            Hi Guy,

             

            Can you please share the expression of prior comparison calculation.

             

            Thanks

            Kumar KVP

              • Re: YTD vs Last YTD Comparison
                Guy Hombleu

                Hello Kumar,

                 

                hereby my variables and calculations:

                 

                vMaxDate =max(%datum_Key)

                vMaxDay =day(max(%datum_Key))

                vMaxMonth =month(max(%datum_Key))

                vMaxYear =max(calendar.Year)

                vMaxYearMonth =vMaxYear&'-'&vMaxMonth

                vPriorMonth =month(addmonths(max(%datum_Key) ,- 1))

                vPriorMonthYear =Year(addmonths(max(%datum_Key) ,- 1))

                vPriorYear =vMaxYear -1

                vPriorYearDate =date(addyears(max(%datum_Key), -1), 'DD MMM YYYY')

                vMaxDate1 =date(addyears(max(%datum_Key), -0), 'DD MMM YYYY')

                 

                Calculation YTD

                =SUM({<calendar.Year={'$(vMaxYear)'},[calendar.Inv date]={'<=$(vMaxDate)'},calendar.Month=,calendar.YearMonth=>}omzet.Linetot)

                 

                Calcation LYTD

                =SUM({<calendar.Year={'$(vPriorYear)'},calendar.Month=,%datum_Key={'<=$(vPriorYearDate)'},calendar.YearMonth=>}omzet.Linetot)

                 

                 

                Can you help me or for the calculation LYTD?

                 

                Many thanks!

                  • Re: YTD vs Last YTD Comparison
                    Ruben Marin

                    You can try manually setting the date, so you can know wich value and wich format is expecting

                     

                    In example, checking if this works for prior year:

                    =SUM({<calendar.Year={'2015'},calendar.Month=,%datum_Key={'<=31-12-2015'},calendar.YearMonth=>}omzet.Linetot)

                     

                    or:

                    =SUM({<calendar.Year={'2015'},calendar.Month=,%datum_Key={"<=31-12-2015"},calendar.YearMonth=>}omzet.Linetot)


                    or:

                    =SUM({<calendar.Year={'2015'},calendar.Month=,%datum_Key={"<=42369"},calendar.YearMonth=>}omzet.Linetot) // 42369 is 31-12-2015 in numeric format


                    When you find the correct syntax you can use the tip of checking the expression in the caption (post below) to see if the expanded value is converted as expected.

                • Re: YTD vs Last YTD Comparison
                  Ruben Marin

                  Hi Guy, the date format of vMaxDate and vPriorYearDate are different, try setting the same format, or changing the format to number.

                   

                  Also, when doing comparisons in set analysis values (like: <=$(vMaxDate)) is better (or necesary) use double quotes instead of simple quotes.

                   

                  As a tip, to show what is doing the $-expansion you can remove the caption of the expression, then hovering the mouse over the caption you can check how are converted vMaxDate and vPriorYearDate.