3 Replies Latest reply on Nov 24, 2018 12:04 PM by Shinichiro Murakami

# Calculated fields with different rolling dates for filter

Hello Everyone,

I am trying to calculate a KPI based on two financial vairables—net profit and total assets. The formula is given by net profit/total assets. The main problem I have is when applying filters to this calculated variable in tableau, since when it aggregates over periods (months, quearters) it sums both variables. But in the calculaiton it should keep the value of total assets fixed to the last month. So the filter distorts the calculation.

I imagine I can achieve proper aggregation for each variable (net profit and total assets)  with an LOD expression, but not sure how to go about it. Should I fix the value of total assetsto MAX([date]) ? {FIXED : MAX([date])}

Would really appreaciate some help here

• ###### 1. Re: Calculated fields with different rolling dates for filter

Could you please attach mocked up sample data set (TWBX is the best)

and . clarify current problem with real number in the sample data.

show the expected value with illustration.

Thanks,

Shin

• ###### 2. Re: Calculated fields with different rolling dates for filter

Hello Shinchiro,

This is the KPI that I need to calculate;

The calculation works perfectly when filtered by month, but it fails when filtered by quarters because the aggregation sums total assets for each month instead of taking the value of the last month for the quarter. So ideally the filter would aggregate the top variable [ net profit], and only take the value of the last month of the quarter for [total assets]... since the value for the last month is already aggregating the assets accumulated throughout the quarter.

The correct value for for Q4, for example, should be: (2245530774 / 93938611183)*100 = 2.39.

I read that this could be achieved with an SQL subquery, but would be great to do it with a calc table.

Thanks !

• ###### 3. Re: Calculated fields with different rolling dates for filter

Could you please attach mocked up sample data set (TWBX is the best)