9 Replies Latest reply on Apr 20, 2018 1:43 AM by Anupam Mehta

    Calculation

    chowdary v

      Hi All,

       

      Here my problem is not working Revenue Calculation here I attached my sample workbook.

       

      This is logic for revenue calculation

       

      how to write below logic in SQL and tableau,

      In the SQL, Create new select columns called BlendedRevenue and BlendedRevenueRunrate. The logic for these columns is to pull either expected value (lead revenue)

       

       

      or revenue based on the product and date. Logic is:

       

       

      For Business loans, pull expectedLeadRevenue instead of matched count

       

       

      For Personal Loans and Auto Loans:

       

       

      -- if it is after the 10th of the month, pull expected revenue for the last month and current month, otherwise matchedRevenue

       

       

      -- if it is before the 10th of the month, pull expected revenue for last 2 months + current month

       

       

      for all other products, pull matched revenue.

       

      Please help me out.

       

       

      Thanks,

      Veeru.

        • 1. Re: Calculation
          kumar bharat

          Hi Veeru,

          As per your requirement you should be checking the day of month but instead you are checking the month.

          to be more precise you have added month(today())>10  instead of day(today()) which is giving incorrect calculations i believe.

          Please recheck the calculation and add day instead of month.

          Hope this helps.

          Thanks,

          Bharat

          • 2. Re: Calculation
            chowdary v

            Hi Kumar,

             

            i already checked month(today()) it is also not working.

             

             

            Thanks,

            Veeru.

            • 3. Re: Calculation
              kumar bharat

              Veeru,

              Ideally You should be adding day(month) in  your calculation but not month(month).

              as per your requirement need is to get data based on either  after or before 10th of the month.

              Please add day in calculation.Hope you understand.

              Thanks,

              Bharat

              • 4. Re: Calculation
                kumar bharat

                Hi Veeru,

                Check the attached,Hope it clarifies.

                Thanks,

                Bharat

                • 5. Re: Calculation
                  chowdary v

                  hi,

                   

                  Already i tried all the ways here i attached my workbook plz check it

                   

                  Thanks,

                  Veeru.

                  • 6. Re: Calculation
                    Anupam Mehta

                    Hi Veeru,

                     

                    Please find the attached resolution of your qery.

                     

                    Regards,

                    Anupam

                    • 7. Re: Calculation
                      chowdary v

                      Hi Anupam,

                       

                      Thanks for your reply I already did this one below one is a solution.

                       

                      sum(case [Product] when 'Personal' then

                      (if month(TODAY()) > 10 then

                      (IF DATETRUNC('month',[QF Complete Date]) = DATETRUNC('month',TODAY()) then [Expected Lead Revenue]

                      elseif DATETRUNC('month',[QF Complete Date]) =

                      DATETRUNC('month',DATEADD('month',-1,TODAY())) THEN [Expected Lead Revenue] END)

                       

                       

                      elseif month(TODAY()) <= 10 then

                      (IF DATETRUNC('month',[QF Complete Date]) = DATETRUNC('month',TODAY()) then [Expected Lead Revenue]

                      elseif DATETRUNC('month',[QF Complete Date]) =

                      DATETRUNC('month',DATEADD('month',-1,TODAY())) THEN [Expected Lead Revenue]

                      ELSEIF DATETRUNC('month',[QF Complete Date]) =

                      DATETRUNC('month',DATEADD('month',-2,TODAY())) THEN [Expected Lead Revenue] END)

                      end)

                       

                       

                      when 'Automobile' then

                      (if month(TODAY()) > 10 then

                      (IF DATETRUNC('month',[QF Complete Date]) = DATETRUNC('month',TODAY()) then [Expected Lead Revenue]

                      elseif DATETRUNC('month',[QF Complete Date]) =

                      DATETRUNC('month',DATEADD('month',-1,TODAY())) THEN [Expected Lead Revenue] END)

                       

                       

                      elseif month(TODAY()) <= 10 then

                      (IF DATETRUNC('month',[QF Complete Date]) = DATETRUNC('month',TODAY()) then [Expected Lead Revenue]

                      elseif DATETRUNC('month',[QF Complete Date]) =

                      DATETRUNC('month',DATEADD('month',-1,TODAY())) THEN [Expected Lead Revenue]

                      ELSEIF DATETRUNC('month',[QF Complete Date]) =

                      DATETRUNC('month',DATEADD('month',-2,TODAY())) THEN [Expected Lead Revenue] END)

                      end)

                      when 'Business Loan' then [Expected Lead Revenue] else [Match Revenue]

                       

                       

                      end)

                       

                      Thanks,

                      Veeru.

                      1 of 1 people found this helpful
                      • 8. Re: Calculation
                        Tim Dines

                        This one works.

                        • 9. Re: Calculation
                          Anupam Mehta

                          Hello Veeru,

                          The calculation you have crated is based on month part i.e if month is greater than 10 the perform a calculation else perform B calculation, so whenerver you run report from January to October it will run part A only.

                          And in problem statement you are looking calculation based on day please find  below

                          - if it is after the 10th of the month, pull expected revenue for the last month and current month, otherwise matchedRevenue

                          - if it is before the 10th of the month, pull expected revenue for last 2 months + current month

                           

                          So it will not work as expected.

                           

                          Please find the below solution, its returning expected result

                           

                          CASE [Product]

                          WHEN 'Business Loan' THEN [Expected Lead Revenue]

                          WHEN  'Personal' THEN (IF (DAY(TODAY()) >= 10) THEN (IF (DATETRUNC('month',[QF Complete Date]) >= DATETRUNC('month',DATEADD('month',-1,TODAY()))) THEN [Expected Lead Revenue] END) ELSE (IF (DATETRUNC('month',[QF Complete Date]) >= DATETRUNC('month',DATEADD('month',-2,TODAY()))) THEN [Expected Lead Revenue] END) END)

                          WHEN  'Automobile' THEN (IF (DAY(TODAY()) >= 10) THEN (IF (DATETRUNC('month',[QF Complete Date]) >= DATETRUNC('month',DATEADD('month',-1,TODAY()))) THEN [Expected Lead Revenue] END) ELSE (IF (DATETRUNC('month',[QF Complete Date]) >= DATETRUNC('month',DATEADD('month',-2,TODAY()))) THEN [Expected Lead Revenue] END) END)

                          ELSE [Match Revenue] END

                            

                          Regards,
                          Anupam