calculated field for month can't sum up when trying for quarter

Question

I'm trying to calculate the quarterly returns as compound.
Formula:-
(1+M1)*(1+M2)*...*(1+Mn)-1
M1 = first month returns.
M2 = second month returns.

Now, I didn't find any which can achieve this. Did I miss anything here?
So, I created two calculated fields [M-1], [M-2] which have previous month's returns

([M-1] = LOOKUP(ATTR([Monthly Return]), -1), [M-2] = LOOKUP(ATTR([Monthly Return]), -2))

And calculating new field QR with formula:

IF(ATTR([Month]) = 3 OR ATTR([Month]) = 6 OR ATTR([Month]) = 9 OR ATTR([Month]) = 12)
THEN
    ((1+ATTR([Monthly Return]))*(1+[M-1])*(1+[M-2]))-1
ELSE
    0
END

This gives me as

Which is correct so far. Now when I try for quarter, it should SUM up all three month's values and give me for Q1 right? Instead it gives me 0 or null as below:

can find tbwx file in here. Why is this happening? Is there something I missed? Is there any other approach where I can achieve this?

Thanks in advance.

Answer 1

Thing is, table calculations (LOOKUP is an example) depends on the level of aggregation explicit on the worksheet.

Take a look, nowhere in your [M-1] and [M-2] calculation you tell Tableau you want it to be performed on months. So in your quarter example, it is basically taking the last quarter values, not months. And you are using ATTR, that may have unpredictable results when not all the values for each dimension are the same.

What I think is happening: ATTR([Month]) is not returning 3, 6, 9 or 12 for any of the quarters (I would guess Tableau would take the first value, therefore 1, 4, 7, 10), and therefore it falls on the ELSE 0 loop.

EDIT: What can be done? If your first chart is correct, you can hide the unnecessary fields. One way to do this is actually hiding the months you're not interested in. Other way is more pragmatic, but it is a little hack.

Create a field ([filter months]):

LOOKUP(MAX([Month]),0)%3 = 0

And drag this to filters, and select true values

This will keep only the months you are interested in (multiples of 3), but won't screw your calculation, because table calculations (like lookup) are performed at last, after everything was already calculated. Therefore, the filtered months won't appear in your chart, but will still be considered in other calculations.

You still need to keep the [Month] dimension on the chart, but you can always play with font size and color to hide it

Answer 2

Formula which is used for calculating compounded quarterly returns is:
(1+M1)*(1+M2)*...*(1+Mn)-1
M1 = first month returns of that quarter.
M2 = second month returns of that quarter.
and calculating this only for months 3,6,9 and 12 with IF ELSE statement shown in the question.
So, this works fine in monthly view as can see in pic-1(in question). For quarter it should be like,
Instead it gives me 0's or null's as in pic-2(in question)

It's because, It's aggregating measures for quarterly and applying the formula((1+M1)*(1+M2)*...*(1+Mn)-1) for aggregated measures. That is, it's calculating SUM([Monthly Return]) for quarter and applying the formula after that. It has to calculate with the formula first and SUM the results.

What I did was just uncheck the Aggregate Measures from Analysis.

Which won't aggregate the values before calculating.