9 Replies Latest reply: Jun 9, 2017 7:18 AM by Sunny Talwar RSS

    Total Revenue by Product Family

    Alice Wong

      Hi,

      I need help to get the total revenue by Product Family with a few conditions below:

      1. Rolling 1 year data (from previous workweek back to 1 year ago, total 52 weeks)

      2. for certain Customer Category

      3. for certain Revenue Channel

      4. for Customer with cumulative total revenue for that rolling 1 year >= $10,000.

       

      I have tried with below expression but it does not work. Please help.

       

      SUM(

        AGGR(

          IF(

                  SUM({<$>*<

                  REV_WEEK = {">=$(=(vThisWeek)-100) <$(=(vThisWeek))"},

                  CUST_CAT = {"CAT B DISTRIBUTION","CAT B DIRECT"},

                  REV_CHANNEL = {"Y"}>}

                  REVENUE) >=10000, END_CUSTOMER), FAMILY, END_CUSTOMER, REVENUE))


      Thanks!

      Alice

        • Re: Total Revenue by Product Family
          Sunny Talwar

          What is the issue with the below expression?

            • Re: Total Revenue by Product Family
              Alice Wong

              It doesn't return the correct numbers. Most are zero.

               

              Get Outlook for Android<https://aka.ms/ghei36>

                • Re: Total Revenue by Product Family
                  Sunny Talwar

                  Would you be able to share a sample?

                    • Re: Total Revenue by Product Family
                      Alice Wong

                      Yupe, will upload a sample data here tomorrow (i gotta go now as it's midnight here now). Thx!

                       

                      Get Outlook for Android<https://aka.ms/ghei36>

                        • Re: Total Revenue by Product Family
                          Sunny Talwar

                          Have a good night

                            • Re: Total Revenue by Product Family
                              Alice Wong

                              Hi Sunny,

                               

                              I have created sample data in the attached file. It has a sheet with a table I need. I am able to get the correct count and total 1-year revenue but I cannot get the correct total revenue by family where only customers with total 1-year revenue  >=$10K are included.

                               

                              This is the correct results I want to achieve:

                                  

                              PRODUCT FAMILYCust Count (>=$10k, Rolling 1-Yr) Total 1-Year Revenue 1-Year Revenue (>=$10K)
                              A5$608,088$592,409
                              B9$1,489,445$1,489,445
                              C4$168,194$149,659
                              Total18$2,265,728$2,231,514

                               

                              Thank you!

                              Alice

                                • Re: Total Revenue by Product Family
                                  Sunny Talwar

                                  I have gotten very close using this

                                   

                                  Sum({<WEEK = {'>=$(=(vThisWeek)-100) <$(=(vThisWeek))'}, FORECAST_CUST_CAT={'CAT B DISTRIBUTION','CAT B DIRECT'}, REV_CHANNEL ={'Y'},

                                  END_CUSTOMER = {"=SUM({<WEEK = {'>=$(=(vThisWeek)-100) <$(=(vThisWeek))'}, FORECAST_CUST_CAT={'CAT B DISTRIBUTION','CAT B DIRECT'}, REV_CHANNEL ={'Y'}>}DOLLARS) > 10000"}>} DOLLARS)

                                   

                                  Capture.PNG

                                   

                                  or this

                                  Sum({<WEEK = {'>=$(=(vThisWeek)-100) <$(=(vThisWeek))'}, FORECAST_CUST_CAT={'CAT B DISTRIBUTION','CAT B DIRECT'}, REV_CHANNEL ={'Y'}>}

                                  Aggr(If(SUM({<WEEK = {'>=$(=(vThisWeek)-100) <$(=(vThisWeek))'}, FORECAST_CUST_CAT={'CAT B DISTRIBUTION','CAT B DIRECT'}, REV_CHANNEL ={'Y'}>}DOLLARS) >= 10000,

                                  Sum({<WEEK = {'>=$(=(vThisWeek)-100) <$(=(vThisWeek))'}, FORECAST_CUST_CAT={'CAT B DISTRIBUTION','CAT B DIRECT'}, REV_CHANNEL ={'Y'}>}DOLLARS)), END_CUSTOMER, [PRODUCT FAMILY]))

                                   

                                  Capture.PNG

                                    • Re: Total Revenue by Product Family
                                      Alice Wong

                                      Hi Sunny,

                                       

                                      The latter works perfect! Thanks!

                                      How to edit this if I need to generate the customer count and revenue for different revenue bucket?

                                      E.g. >=10,000 to <50,000, >=50,000 to <100,000.

                                       

                                      Thanks,

                                      Alice

                                        • Re: Total Revenue by Product Family
                                          Sunny Talwar

                                          May be this

                                           

                                          >=10,000 to <50,000

                                          Sum({<WEEK = {'>=$(=(vThisWeek)-100) <$(=(vThisWeek))'}, FORECAST_CUST_CAT={'CAT B DISTRIBUTION','CAT B DIRECT'}, REV_CHANNEL ={'Y'}>}

                                          Aggr(


                                          If(SUM({<WEEK = {'>=$(=(vThisWeek)-100) <$(=(vThisWeek))'}, FORECAST_CUST_CAT={'CAT B DISTRIBUTION','CAT B DIRECT'}, REV_CHANNEL ={'Y'}>}DOLLARS) >= 10000


                                          and


                                          SUM({<WEEK = {'>=$(=(vThisWeek)-100) <$(=(vThisWeek))'}, FORECAST_CUST_CAT={'CAT B DISTRIBUTION','CAT B DIRECT'}, REV_CHANNEL ={'Y'}>}DOLLARS) < 50000


                                          ,

                                          Sum({<WEEK = {'>=$(=(vThisWeek)-100) <$(=(vThisWeek))'}, FORECAST_CUST_CAT={'CAT B DISTRIBUTION','CAT B DIRECT'}, REV_CHANNEL ={'Y'}>}DOLLARS)), END_CUSTOMER, [PRODUCT FAMILY]))


                                          >=50,000 to <100,000

                                          Sum({<WEEK = {'>=$(=(vThisWeek)-100) <$(=(vThisWeek))'}, FORECAST_CUST_CAT={'CAT B DISTRIBUTION','CAT B DIRECT'}, REV_CHANNEL ={'Y'}>}

                                          Aggr(


                                          If(SUM({<WEEK = {'>=$(=(vThisWeek)-100) <$(=(vThisWeek))'}, FORECAST_CUST_CAT={'CAT B DISTRIBUTION','CAT B DIRECT'}, REV_CHANNEL ={'Y'}>}DOLLARS) >= 50000


                                          and


                                          SUM({<WEEK = {'>=$(=(vThisWeek)-100) <$(=(vThisWeek))'}, FORECAST_CUST_CAT={'CAT B DISTRIBUTION','CAT B DIRECT'}, REV_CHANNEL ={'Y'}>}DOLLARS) < 100000


                                          ,

                                          Sum({<WEEK = {'>=$(=(vThisWeek)-100) <$(=(vThisWeek))'}, FORECAST_CUST_CAT={'CAT B DISTRIBUTION','CAT B DIRECT'}, REV_CHANNEL ={'Y'}>}DOLLARS)), END_CUSTOMER, [PRODUCT FAMILY]))