7 Replies Latest reply: Aug 9, 2018 8:15 AM by Mahitha M RSS

    YTD to Single month data

    Mahitha M

      Hi Experts,

       

      Can any one please help me on below requirement.

      I have two years of data in my app i.e, 2017 and 2018.

       

      In the below chart each month sum(Sales) is the YTD amount from this YTD amount how to get individual month amount.

      For example Dec month amount is amount from Dec - amount from Nov.

      Please help me on this.

       

      Thanks in advance

        • Re: YTD to Single month data
          Sunny Talwar

          May be this

           

          Sum(Sales) - Above(Sum({<Month>}Sales))

            • Re: YTD to Single month data
              Mahitha M

              Hi Sunny,

               

              Thanks for your reply. I am getting different issue here.

              Please find the below YTD Values Bar chart from 2017 and 2018.

              I have used the below expression

              2018= Sum({<Year={'$(=Year(Max(Date)))'}>}Sales)/1000000

              2017= Sum({<Year={'$(=Year(Max(Date))-1)'}>}Sales)/1000000

               

              As per the requirement I need to convert the above YTD values to monthly values For example Dec month amount is amount from Dec - amount from Nov.


              I have tried like as you mentioned above like this.


              2018= Sum({<Year={'$(=Year(Max(Date)))'},Month>}Sales)/1000000 - Above(Sum({<Year={'$(=Year(Max(Date)))'},Month>}Sales)/1000000)

              2017= Sum({<Year={'$(=Year(Max(Date))-1)'},Month>}Sales)/1000000-Above(Sum({<Year={'$(=Year(Max(Date))-1)'},Month>}Sales)/1000000)

               

              but getting results like below. In 2018 I have the data up to Jun but getting Jul bar also and for Jan I am not getting that month value from YTD. Please help me on this.

                • Re: YTD to Single month data
                  Sunny Talwar

                  May be do this for 2018

                   

                  If(Sum({<Year={'$(=Year(Max(Date)))'},Month>}Sales) <> 0,

                  Sum({<Year={'$(=Year(Max(Date)))'},Month>}Sales)/1000000 - Above(Sum({<Year={'$(=Year(Max(Date)))'},Month>}Sales)/1000000))

                    • Re: YTD to Single month data
                      Mahitha M

                      Hi Sunny,

                       

                      Thanks for your reply. 2018 negative bar was removed.

                      But still I am not able to see jan month bars data.

                      For 2018 and 2017 jan month need to get actual values i.e., 12.19 and 9.74.

                      By above() the jan month showing no values.

                      • Re: YTD to Single month data
                        Mahitha M

                        Hi stalwar1,

                         

                        I have tried this expression for 2017

                         

                        =If(Rowno()=1,

                        Sum({<Year={'$(=Year(Max(Date))-1)'},Month>}Sales)/1000000,

                        Sum({<Year={'$(=Year(Max(Date))-1)'},Month>}Sales)/1000000-Above(Sum({<Year={'$(=Year(Max(Date))-1)'},Month>}Sales)/1000000)

                        )


                        now I got 2017 jan bar

                        could you please help me to apply  this logic in 2018 expression. here 2 if logics need to add.

                        =If(Rowno()=1,

                        Sum({<Year={'$(=Year(Max(Date)))'},Month>}Sales)/1000000,




                        If(Sum({<Year={'$(=Year(Max(Date)))'},Month>}Sales) <> 0,

                        Sum({<Year={'$(=Year(Max(Date)))'},Month>}Sales)/1000000 - Above(Sum({<Year={'$(=Year(Max(Date)))'},Month>}Sales)/1000000))


                        Thanks in advance

                        • Re: YTD to Single month data
                          Sunny Talwar

                          Try this for 2018

                           

                          If(Sum({<Year={'$(=Year(Max(Date)))'},Month>}Sales) <> 0,

                          RangeSum(Sum({<Year={'$(=Year(Max(Date)))'},Month>}Sales)/1000000, -Above(Sum({<Year={'$(=Year(Max(Date)))'},Month>}Sales)/1000000)))

                           

                          2017

                          RangeSum(Sum({<Year={'$(=Year(Max(Date))-1)'},Month>}Sales)/1000000, -Above(Sum({<Year={'$(=Year(Max(Date))-1)'},Month>}Sales)/1000000))