1 of 1 people found this helpful
For one thing, you can't have an aggregate within an aggregate.
You have an AVG inside a SUM.
TO aggregate aggregates, use WINDOW_SUM instead of SUM in that calc. But everything inside a table calc will need to be aggregated, so inside the WINDOW_SUM parens, do ATTR([Account type]) instead of just [account type], for example. All the fields inside the table calc need this.
You might be able to do that AVG in a FIXED LOD, which would then let you include it in the SUM() logic instead.
A sample workbook here would really help me show you some of this.
1 of 1 people found this helpful
you have an aggregation in there somewhere - my guess is [Post Shares (SUM)]
when that happens the other dimensions and measures need to be aggregated like account type needs to be attr(account type)
but when you get all that fixed there is another issue - the statement below will always be FALSE - because if account type = instagram = true will trigger the first clause
ELSEIF [Shared?] = “Yes” AND [Account Type] != “Instagram” AND [Post Shares (SUM)] = 0
if you wan this to work make the second clause the first clause - then move the first clause to the else if statment
If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution. Thank you.
Hi @JoeOppelt and @JimDehner, thanks for your help! I feel like I'm close, just struggling with LOD's (I know a fair amount Tableau but am still learning LOD's and the power of calculated fields). I've attached a sample workbook here.
Here's some context for what I am trying to achieve. The social media publishing tool that we use aggregates impressions (aka reach) that come from sharing a post beyond our original audience. This skews another calculation that we use (Engagement Rate = Total Engagements/Impressions) which makes our content look like it performed poorly if it was shared because the impression number becomes larger than it's supposed to. Because of this, we are trying to make a calculated field that looks at whether a post has more than 0 shares and if it does, take the average reach for posts that have no shares in order to normalize impressions. Instagram does not provide impressions anymore so, we are taking 30% of followers at time of post to derive an estimate.
LOD Example.twbx 29.9 KB
This compiles cleanly:
IF attr([Social Network]) = "Instagram"
THEN 0.3 * attr([Followers At Post Time (SUM)])
ELSEIF ATTR([Shared?]) = "Yes"
if attr([Post Shares (SUM)]) = 0 then AVG([Post Reach (SUM)]) END
But I'm not sure that really what you want to do.
What are you trying to accomplish here?
That calc is doing this:
If network is instagram, do .3 times the value of [Followers...]
The ELSEIF is handling all non-instagram stuff, so no extra check on that is needed. (Tableau evaluates from top down. And when it hits a TRUE condition, it stops evaluating. So all Instagram stuff is captured at the top.)
So when we're down in the non-instagram part, there is another check. If SHARED then see if Post Shares = 0. If so, then grab the value of post-reach.
Every other condition evaluates to NULL.
All the ATTR() stuff is necessary because of the AVG() aggregation.
Do you want the calc evaluated for every ROW of data? Or do you want it evaluated at some dimensional rollup? If you want it done for each row, then you can take off all the ATTR and AVG stuff. Then it becomes another measure value computed at the row level rather than at the aggregate level.
And if you want it done at the aggregate level for the dimensions on the sheet, then
attr([Post Shares (SUM)]) = 0
needs to be
sum([Post Shares (SUM)]) = 0
This is exactly what I need! THANK YOU!!!