Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
What is the issue with the below expression?
It doesn't return the correct numbers. Most are zero.
Get Outlook for Android<https://aka.ms/ghei36>
Would you be able to share a sample?
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>
Have a good night
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
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]))
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
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]))