9 Replies Latest reply on Apr 15, 2019 9:03 PM by Mahfooj Khan

# Product function (Excel) on Tableau, and Time series analysis

Hello All,

Was working and got stucked on calculating some time sierial analysis, which is using PRODUCT function (Excel).

Since Tableau doesn't offers the PRODUCT function (multiplying values), I have refered the fomula " Running Product = EXP(RUNNING_SUM(LN(SUM([Data1]))))" from Running Product by Alex.

I prepped some sample on Excel which the blue columns are given, Orange columns I calculated on Tableau, Yellow columns are my goal to calculate.

What I am trying to calculate is to calculate yellow columns by multiplying the values of Maintain% (1-Churn%) inside of each TERM (yearly).

And I got stucked on calculating yellow columns and Tableau says "Argument to SUM (an aggregate function) is already an aggregate, and can not be futher aggregate",

which means I already created CHURN% as SUM(LEFT)/SUM(EXIST) and MAINTAIN% as 1-[CHURN%] and they are AGG(CHURN%) and AGG(MAINTAIN%) already.

So, How can I use PRODUCT function in this circumstance and figure out yellow columns? Please help.

I attached both Excel file and Tbwx file below so please refer.

• ###### 1. Re: Product function (Excel) on Tableau, and Time series analysis

Hi,

Is that what you were looking for?

Workbook attached for your reference. Let us know if you've any question.

Mahfooj

1 of 1 people found this helpful
• ###### 2. Re: Product function (Excel) on Tableau, and Time series analysis

Hi, Mahfooj

This is great! Thank you for your solution.

I honestly have one more step to calculate after that, which is Exist * PRODUCT() and Exist * PRODUCT()/12.

So It will be prefect If I can have all same PRODUCT() value for every TERM(month) instead of a PRODUCT() at the end of each TERM(month). This can be happened?

Again, great job Mahfooj!

1 of 1 people found this helpful
• ###### 3. Re: Product function (Excel) on Tableau, and Time series analysis

Here you go Ben!

Hope this will serve the purpose. Kindly mark helpful/correct on my replies if it helps you to achieve the correct output.

Mahfooj

1 of 1 people found this helpful
• ###### 4. Re: Product function (Excel) on Tableau, and Time series analysis

Jesus, you are amazing!

I can't not say how thank I am.

1 of 1 people found this helpful
• ###### 5. Re: Product function (Excel) on Tableau, and Time series analysis

You're welcome

1 of 1 people found this helpful
• ###### 6. Re: Product function (Excel) on Tableau, and Time series analysis

Hi Mahfooj,

hope you are well.

I know I already got lots of help from you but stuck again.

I was asked to fold up or take the TERM(month) out of the table view with keeping the value of "1-Product()" for TERM(year).

I have tried some way such as combined filed or hierarchy already. Since the Product() and 1 - Product() measure work with TERM(Month), it pops out as error marks for both Product() and 1- Product() marks.

The table I am looking for looks like under:

Sincerely,

• ###### 7. Re: Product function (Excel) on Tableau, and Time series analysis

Hi Ben,

I'm doing good. Not very active in community these days due to some other priority works

However, I have got chance to look into your issue and this is what I have got. Hope this will help. Take a look.

Mahfooj

1 of 1 people found this helpful
• ###### 8. Re: Product function (Excel) on Tableau, and Time series analysis

Mahfooj, hope they went well.

Jesus, you are literally amazing!

I was not sure this can be solved this time while I was uploading this question.

Great thanks to you

• ###### 9. Re: Product function (Excel) on Tableau, and Time series analysis

Welcome Ben! Yeah it was manageable