1 2 Previous Next 29 Replies Latest reply on Mar 2, 2017 8:39 AM by Deepak Rai

# How to create a calculated field based on the SUM of a column

Hi all,

I want to create a calculated field that categorizes my data based on the SUM of "Total MV". Right now I have the following query on "Category" calculated field :

if [Total MV] > 250000 and [Total MV] < 499999 then "AUM: \$250K to \$499K"

elseif [Total MV] > 500000 and [Total MV] < 999999 then "AUM: \$500K to \$999K"

elseif [Total MV] > 1000000 then "AUM: \$1MM+"

END

However, the following results do not make sense:

and many more. I want the resulting category be based on the sum on "Total MV", however the results are not making sense.

Any help would be appreciated. I am attaching my workbook so you guys can take a look.

Thanks a lot,

• ###### 1. Re: How to create a calculated field based on the SUM of a column

Please check the calac and the result. You are receieving few nulls, because you are statring the category from 250000. If you would start from 0 then those NUlls would go away. Rest all looks good.

Thanks

Deepak

2 of 2 people found this helpful
• ###### 2. Re: How to create a calculated field based on the SUM of a column

Thank you very much Deepak! Worked just fine. Deepak would you know any way of showcasing this data based on the following parameters: Lastdayofyear, Total MV, 250-499k, 500-999k, 1M+ accounts like the following:

Right now I have structured the data as Total MV, and then the other measure names. However, what I would like would be to have Total MV, and on top of Total MV have each year the accounts with 250-499k, 500-999k, 1M+. Is it possible to add the category into the bar chart?

Thank you for all your help,

• ###### 3. Re: How to create a calculated field based on the SUM of a column

Yes, Bring Category to Columns after Year. Try otherwise attach the workbook with this view .

Thanks

Deepak

1 of 1 people found this helpful
• ###### 4. Re: How to create a calculated field based on the SUM of a column

If I add the category to columns it just shows below all 1M+ to all the years. However, for all years I just want to show Total Mv, and the accounts that make up for 250-499k, 500-999k, and 1M+. Attached is the workbook, Again thank you for all your efforts.

• ###### 5. Re: How to create a calculated field based on the SUM of a column

Check the views and use whichever you like.

OR

I created a new calc for this and brought to columns:

Attached is in 10.2 (b), so you may not be able to open it.

Thanks

Deepak

1 of 1 people found this helpful
• ###### 6. Re: How to create a calculated field based on the SUM of a column

Here is attachment

1 of 1 people found this helpful
• ###### 7. Re: How to create a calculated field based on the SUM of a column

Awesome! Just how I wanted it. My last step would be to create a drill down action on the bar charts. For example If I were to click the 2008 250-499k bars, my results on my data sheet would filter to show only results for 2008 and accounts corresponding to 250-499k... and same for the other years and categories. Therefore, I am thinking it would be a filter on category and year. Would that be possible? I have the following set up:

And I am trying to filter out the results based on: Dashboard > Actions > Add Action > Filter> ..... But which ones would be my source and target sheets? and if any.. would there be specific selected fields under "target filters". Thank you Deepak

And I would like to run it on select. Attached is my added work on the sheet.

• ###### 8. Re: How to create a calculated field based on the SUM of a column

For the Drill down action on year this is the one:

I am now trying to filter out based on category. Any ideas would be awesome. Thank you!!

• ###### 9. Re: How to create a calculated field based on the SUM of a column

Is it Working for you? I am getting an error. "Missing fields for HNW Chart"

• ###### 10. Re: How to create a calculated field based on the SUM of a column

I have an SQL version of this and it is working for the date there. If I do it in the excel version I am also getting missing fields when I am trying to filter out for date. However do you think it would be possible to filter out based on category?

• ###### 11. Re: How to create a calculated field based on the SUM of a column

Looks like you need this!!!

I used this combination, edit my logic in action filter to modify as per your need.

1 of 1 people found this helpful
• ###### 12. Re: How to create a calculated field based on the SUM of a column

Hi deepak,

Regarding the chart that you helped me create based on category 250-499,500-999k, 1MM+ the data does not seem to be consistent with the below representation of the same graph but in older MSOffice chart. As you can observe also in the attached workbook, there are more 250-499k accounts as well as 500-999k accounts than 1MM+ accounts. However, the tableau chart does not conform with the older version of it (see attached). I am thinking it might be a problem in the LOD that was created ( {FIXED [Account Name]:[Category (copy)]} ). Moreover, if you look at the "data" sheet in the attached workbook you can also see that there is more data for 250-499k, and 500-999k instead of 1MM+. So I can not seem to grasp where it can be that the mistake might be that the tableau chart does not show accordingly:

it seems to be that the order might be switched, meaning that the 1M+ chart reflects the 250-499k and viceversa. Moreover, my category query was the following:

if SUM([Total MV]) > 250000 and SUM([Total MV]) <= 499999 then "AUM: \$250K to \$499K"

elseif SUM([Total MV]) > 500000 and SUM([Total MV]) <= 999999 then "AUM: \$500K to \$999K"

elseif SUM([Total MV]) >= 1000000 then "AUM: \$1MM+"

END

Any ideas would be really helpful. Thank you

• ###### 13. Re: How to create a calculated field based on the SUM of a column

I see where the problem was, Actually, we fixed it to Account Name . It should have been fixed to Account number. I made Changes.Pl check attached.

Thanks

Deepak

• ###### 14. Re: How to create a calculated field based on the SUM of a column

Hi deepak,

I changed it to fixed [Account Number] but it continues to show similar results, where 1MM+ accounts is bigger than 250-499k and 500-999k, again not the same as the old MSoffice chart. See attached. Thanks for the efforts. The calculation 1 that you have I have it named in my chart as "AUM"