Total Revenue by Product Family

[#1]

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

[sunny_talwar]

What is the issue with the below expression?

[#3]

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

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

[sunny_talwar]

Would you be able to share a sample?

[#5]

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>

[sunny_talwar]

Have a good night

[#7]

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

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

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

[#9]

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

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