Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Not applicable

Total Revenue by Product Family

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

9 Replies

Re: Total Revenue by Product Family

What is the issue with the below expression?

Not applicable

Re: Total Revenue by Product Family

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

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

Re: Total Revenue by Product Family

Would you be able to share a sample?

Not applicable

Re: Total Revenue by Product Family

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

Have a good night

Not applicable

Re: Total Revenue by Product Family

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

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

Not applicable

Re: Total Revenue by 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

Re: Total Revenue by Product Family

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

Community Browser