2 Replies Latest reply on Jul 19, 2018 5:41 AM by jo.bi

    Cumulative Sales with variable for next months

    jo.bi

      I want to display some forecast numbers.

      For next month, I want to add a variable to current month's figures.

      Let's say i have 8600 in July, and I want to add my variable (500) that will show next month figures = 9100 and the month after, I want to show 9100 + my variable (300) so 9400. and so forth..


      I tried using DATETRUNC, RUNNING_SUM, WINDOW_MAX but couldn't figure out the right numbers/formula..
      How is cumulative function working on Tableau? Anyone could please help me out?


      Here are attached a twbx and Excel mock-up.

      Thanks in advance,

        • 1. Re: Cumulative Sales with variable for next months
          Sankarmagesh Rajan

          hi Jo

           

          running sum will work based on value available.

           

          it will add the first and second value and it will show total in 2nd month like that.

          move your starting number into measure.

           

          You can try this

          if attr(MONTH([Prd Dt]))= 8 then (if  ISNULL(min([starting number])) or min([starting number])=0 THEN PREVIOUS_VALUE(min([starting number]))+(500)

          END)

          elseif attr(MONTH([Prd Dt]))= 9 then (if  ISNULL(min([starting number])) or min([starting number])=0 THEN PREVIOUS_VALUE(min([starting number]))+(300)

          END)

          ELSE min([starting number])

          end

           

          if you want to go with some percent increase then  easy.

           

          IF ISNULL(SUM([Sales])) or SUM([Sales])=0 THEN PREVIOUS_VALUE(SUM([Sales]))*(1+[New Business Growth])

          ELSE SUM([Sales])

          END

           

          thanks

          sankar

          • 2. Re: Cumulative Sales with variable for next months
            jo.bi

            Sankar,

             

            Thanks a lot for your help. It's hardcoding but I think there's no other way at the moment.

            You rock!

            Jo Bi