This content has been marked as final.
Show 1 reply

1. Re: How to exclude dimensions in time series if current month is 0
Tom W Jul 21, 2017 10:58 AM (in response to Daniela Shasha)ZN(
SUM(
IF
{FIXED [Job] : MAX(DATETRUNC('month',Date))} =
{FIXED : MAX(DATETRUNC('month',Date))}
THEN
Spent
end
)
)
>0
Break it down;
 ZN() will return 0 if the calculation inside ends up being null
 I'm taking a SUM of a calculation
 The IF statement is checking two level of detail calculations against each other (the level of detail calculation being the pieces inside the { }
IF the highest month (MAX) for the Job equals the highest month in the entire dataset, then return the Spent field.  Because we wrap that IF in the SUM, we're effectively doing a SUM of [Spent] WHEN the maximum month for the job matches the maximum month for the entire dataset
 At the end I'm checking if the SUM(....) is greater than zero. Thus this formula will return TRUE or FALSE
 Why am I using datetrunc? I'm using datetrunc to standardize your date field at the month level as daily sales would throw this off.
Put this new calculation on the filters shelf and set it to true.