
1. Re: Trying to calculate but the calculation is not summing the entire values
Simon Runc Jun 7, 2018 6:31 AM (in response to Shea Moyner)hi Shea,
Sure it's me!, but not sure I completely understand your requirement. What do you want the Allowed2 column to show? A sum of all Par and Non Par for each State/Claim Type/Claim Type 2?
If you let me know I'll take a look

2. Re: Trying to calculate but the calculation is not summing the entire values
Shea Moyner Jun 7, 2018 6:57 AM (in response to Simon Runc)I have other code in there as well. The WBX does not give the full extent of my issue.
I have an IF that says:
IF [Par Status] = 'PAR' AND [In Network Code] = 'OUT'
THEN 'PAR'
ELSEIF [Par Status] = 'PAR' AND [In Network Code] = 'IN'
THEN 'PAR'
ELSEIF [Par Status] = 'NONPAR' AND [In Network Code] = 'IN'
THEN 'PAR'
ELSEIF [Par Status] = 'NONPAR' AND [In Network Code] = 'OUT'
THEN 'NONPAR' END
This produces for example when I add the dollars:
I wrote the Allowed2 code to try and perform the summations which should end up as:
CO NONPAR OUT NONPAR 30,575068.43
CO PAR PAR 1,268,950,812.27 This is a summation of anything that is claimtype2 and is in the PAR bucket for that state

4. Re: Trying to calculate but the calculation is not summing the entire values
Shea Moyner Jun 7, 2018 7:16 AM (in response to Simon Runc)No, I need to sum the data and I cannot remove those items otherwise the IF does not work.

5. Re: Trying to calculate but the calculation is not summing the entire values
Bryce Larsen Jun 7, 2018 7:19 AM (in response to Shea Moyner)Hi Shea Moyner,
Is this close to what you're looking for?
It ignores Network Code and the calculated Par Status to aggregate "Allowed" based on ClaimType2.
Alternatively, you can look to use a table calc with it so the total value per ClaimType2 by State only shows up on the final row:
EDIT: modified banding so it's clear the ClaimType2 rows are aggregated together.
Please let me know your thoughts  happy to share if accurate!
Best,
Bryce

6. Re: Trying to calculate but the calculation is not summing the entire values
Shea Moyner Jun 7, 2018 7:22 AM (in response to Shea Moyner)IF ATTR([ClaimType2]) = 'PAR' AND ATTR([In Network Code])
= 'IN' OR
ATTR([ClaimType2]) = 'PAR' AND ATTR([In Network Code])
= 'OUT'
THEN WINDOW_SUM([Allowed2])
ELSEIF ATTR([ClaimType2]) = 'NONPAR'
THEN WINDOW_SUM([Allowed2]) END
Even trying this and doing pane down it will sum the par IN together but it will not grab the par out and apply to par IN. I want one overall sum using the code as:
ClaimType2:
IF [Par Status] = 'PAR' AND [In Network Code] = 'OUT'
THEN 'PAR'
ELSEIF [Par Status] = 'PAR' AND [In Network Code] = 'IN'
THEN 'PAR'
ELSEIF [Par Status] = 'NONPAR' AND [In Network Code] = 'IN'
THEN 'PAR'
ELSEIF [Par Status] = 'NONPAR' AND [In Network Code] = 'OUT'
THEN 'NONPAR' END
Allowed2:
IF ATTR([ClaimType2]) = 'PAR'
THEN SUM([Allowed])
ELSEIF ATTR([ClaimType2]) = 'NONPAR'
THEN SUM([Allowed]) END
Sum Allowed:
IF ATTR([ClaimType2]) = 'PAR' AND ATTR([In Network Code])
= 'IN' OR
ATTR([ClaimType2]) = 'PAR' AND ATTR([In Network Code])
= 'OUT'
THEN WINDOW_SUM([Allowed2])
ELSEIF ATTR([ClaimType2]) = 'NONPAR'
THEN WINDOW_SUM([Allowed2]) END

7. Re: Trying to calculate but the calculation is not summing the entire values
Bryce Larsen Jun 7, 2018 7:31 AM (in response to Shea Moyner)Hi Shea Moyner
Is my response above close? Per your comment Re: Trying to calculate but the calculation is not summing the entire values it seems you want to sum Allowed along State and ClaimType2.
I did this by using an {EXCLUDE} LOD expression. We could look to do a {FIXED} statement if that'd be of more use  it all depends on what vizzes you'd look to display this in.

8. Re: Trying to calculate but the calculation is not summing the entire values
Simon Runc Jun 7, 2018 7:40 AM (in response to Shea Moyner)Not sure I understand. Which IF statement?
Your [Par Status] AND [In Network Code] are rowlevel dimensions, so the logic will work whether they are in the view or not. Tableau thinks differently from Excel, for example, where you would need these in the view for the IF to work.
If you can let me know what values and rows you want for a single State and any other fields and I should be able to work it out.

9. Re: Trying to calculate but the calculation is not summing the entire values
Shea Moyner Jun 7, 2018 7:57 AM (in response to Simon Runc)I have no idea what banding and trying to research the whole exclude lod fixed information online. The learning in Tableau is like incredibly basic. I need to sum:
So, I am showing headers now so where the claimtype2 = nonpar I want to sum allowed showing one time. Where the claimtype2 = par and includes the in and out and your can see par status has par and nonpar which is correct because my if statement for claimtype2 forced that. So, now I want to show a single sum for allowed for the par so BCC for example would show up one time and have the summation = 7,747,735,655.18.
This is a summation of 1,091,027.20 + 7,746,090,471.68 + 554,156.30. I just cannot figure out how to manipulate any calc or doing what is called banding to get to that point.

10. Re: Trying to calculate but the calculation is not summing the entire values
Simon Runc Jun 7, 2018 8:41 AM (in response to Shea Moyner)So I think this will do the trick
SUM(
IIF([ClaimType2]='PAR',[Allowed],NULL)
)
and we can the remove all the other dimensions.
btw in the version you've posted the ClaimType2 isn't a calculated field, and the numbers are different from your version, but hopefully if you plug that formula in a remove all the dims you should just get the PAR Allowed for each state.
Here we are nesting the rowlevel condition (the IIF part) inside an aggregation (the SUM part)

Anonymous  SR.twbx 1.1 MB


11. Re: Trying to calculate but the calculation is not summing the entire values
Bryce Larsen Jun 7, 2018 8:58 AM (in response to Shea Moyner)Hi Shea Moyner
Yep, that's what my calculation is doing. You can ignore banding  that's just the shading. I thought it'd be useful to group all ClaimType2s together by shading the rows.
To match your example, have a look at NH:
Sum all the rows in PAR: 37658.93 + 1741263.6 + 20494.77 = 1799417.3, which is the displayed value on the right.
Here's my calculation for Allowed2 now:
{exclude [In Network Code], [Par Status]:
SUM([Allowed])}
In order to do what I did in second example, you need to move Allowed to the Rows shelf as well, and then you can drag this onto the Text pane. This is a table calc computed using Specific Dimensions: In Network Code & Par Status.
Hope this helps. Attached a workbook (10.3) if you can have a look.

Anonymous_Bryce_10.3.twbx 1.1 MB


12. Re: Trying to calculate but the calculation is not summing the entire values
Shea Moyner Jun 7, 2018 9:01 AM (in response to Shea Moyner)I figured it out by doing the following:
Created a calculation and called it ParStatus2:
IF [Par Status] = 'PAR' AND [In Network Code] = 'OUT'
THEN 'PAR'
ELSEIF [Par Status] = 'PAR' AND [In Network Code] = 'IN'
THEN 'PAR'
ELSEIF [Par Status] = 'NONPAR' AND [In Network Code] = 'IN'
THEN 'PAR'
ELSEIF [Par Status] = 'NONPAR' AND [In Network Code] = 'OUT'
THEN 'NONPAR' END
Created a calculation and called it Allowed2:
IF (ATTR([ParStatus2]) = 'PAR' AND ATTR([In Network Code]) = 'IN' OR
ATTR([ParStatus2]) = 'PAR' AND ATTR([In Network Code]) = 'OUT' )
THEN TOTAL(SUM([Allowed]))
ELSEIF ATTR([ParStatus2]) = 'NONPAR'
THEN TOTAL(SUM([Allowed])) END'
Created a calculation and called it Sum Allowed:
WINDOW_SUM([Allowed2])
Removed from my Rows shelf everything but Market, ParStatus2, In NetworkCode
Added an Index and set to 1. Added the index to the filters. This is what I am left with:

13. Re: Trying to calculate but the calculation is not summing the entire values
Simon Runc Jun 7, 2018 9:09 AM (in response to Shea Moyner)1 of 1 people found this helpfulGood work Shea. That's some pretty advanced Tableau there!!
As I said the status, claim type field were all materialized (i.e. not formulas) in the posted version, so I probably couldn't see where you needed aggregations in order to create aggregated dimensions. If the [Par Status 2] needs to have a certain level of detail to work, then yes Table Calcs (with the index = 1 trick) is probably the best way to go. LoDs (both FIXED and INCLUDE/EXCLUDE also allow you have a different CalculationLoD from the VizLoD...but Table Calcs are more efficient).