4 Replies Latest reply on Dec 8, 2017 9:40 AM by Shinichiro Murakami

# Help with Calc to determine the pattern

Hello, PFA excel sheet. I'm having trouble determining the calc for the total sales.

I want the yearly sale number to increase as

2019: IF SEQ = 4, then  Total SalesfromTableau *.89

2020: IF SEQ = 5 then Total SalesfromTableau*.89 + lag1(Total SalesfromTableau)*.803

2021: IF SEQ = 6 then Total SalesfromTableau*.89 + lag1(Total SalesfromTableau)*.803 +lag2(Total SalesfromTableau)*.633

As year increases the complexity increases. Also, years from 2016-18 are not considered.

Also, I have to add that Total SalesfromTableau  is a calculated field that is being done in tableau. I want to do this calc in tableau and not outside because it uses a lot of parameters.

• ###### 1. Re: Help with Calc to determine the pattern

Please do prepare something in Tableau as Packaged workbook.

• ###### 2. Re: Help with Calc to determine the pattern

HI Shwetha

I tried to replicate excel cal as much as possible.

But still not sure this is right appraoch or not, cause it's anyways complicated .

Let's assume you have these data.

 Year SEQ Con Num Total SalesfromTableau Lag Runoff no 2016 1 11647788 0 2017 2 12269782.51 0 2018 3 12594604.21 0 2019 4 0 2941553.748 0.89 9,792,882 2020 5 0 2996215.721 0.803 7,237,994 2021 6 0 3024076.859 0.633 4,864,967 2022 7 0 3051937.998 0.547 3,090,160 2023 8 0 3051937.998 0.377 1,556,181 2024 9 0 3051937.998 0.214 670,834 2025 10 0 3051937.998 0.134 278,058 2026 11 0 3051937.998 0.114 132,533 2027 12 0 3051937.998 0.098 44,623

Self Join as below.

Step-1

Step-2

Step-3

Step-4

Regards,

Shin

• ###### 3. Re: Help with Calc to determine the pattern

This is awesome. Exactly what I wanted. Thank you.

• ###### 4. Re: Help with Calc to determine the pattern

Hi Shwetha

You are welcome, but please keep in mid that Tableau is different from Excel.

Regards,

Shin