topic Re: Total Revenue by Product Family in App Development

Total Revenue by Product Family

Thu, 08 Jun 2017 15:58:29 GMT

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 — Thu, 08 Jun 2017 16:01:05 GMT

What is the issue with the below expression?

Re: Total Revenue by Product Family

Thu, 08 Jun 2017 16:05:20 GMT

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 — Thu, 08 Jun 2017 16:08:31 GMT

Would you be able to share a sample?

Re: Total Revenue by Product Family

Thu, 08 Jun 2017 16:13:08 GMT

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 — Thu, 08 Jun 2017 16:14:23 GMT

Have a good night

Re: Total Revenue by Product Family

Fri, 09 Jun 2017 07:26:21 GMT

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 FAMILY	Cust Count (>=$10k, Rolling 1-Yr)	Total 1-Year Revenue	1-Year Revenue (>=$10K)
A	5	$608,088	$592,409
B	9	$1,489,445	$1,489,445
C	4	$168,194	$149,659
Total	18	$2,265,728	$2,231,514

Thank you!

Alice

Re: Total Revenue by Product Family

sunny_talwar — Fri, 09 Jun 2017 09:20:12 GMT

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)

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]))

Re: Total Revenue by Product Family

Fri, 09 Jun 2017 10:39:28 GMT

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 — Fri, 09 Jun 2017 11:18:14 GMT

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]))