13 Replies Latest reply on Jun 7, 2018 9:09 AM by Simon Runc

    Trying to calculate but the calculation is not summing the entire values

    Shea Moyner

      IF ATTR([ClaimType2]) = 'PAR' OR ATTR([ClaimType2]) = ' '

      THEN SUM([Allowed])

      ELSEIF ATTR([ClaimType2]) = 'NON-PAR'

      THEN SUM([Allowed]) END

       

      Need the PAR to sum anything related to PAR and NONPAR anything that is NONPAR. I did a screen-shot and attached an example wb

       

        • 1. Re: Trying to calculate but the calculation is not summing the entire values
          Simon Runc

          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

            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] = 'NON-PAR' AND [In Network Code] = 'IN'

            THEN 'PAR'

            ELSEIF  [Par Status] = 'NON-PAR' AND [In Network Code] = 'OUT'

            THEN 'NON-PAR' 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            NON-PAR          OUT                 NON-PAR     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

            • 3. Re: Trying to calculate but the calculation is not summing the entire values
              Simon Runc

              So you only want a maximum of 2 rows per State/ClaimType2...one summation for PAR and one for NON-PAR?

               

              If you remove ClaimType, In Network Code and Par Status from you view...does that do the trick?

               

              Like this

               

              • 4. Re: Trying to calculate but the calculation is not summing the entire values
                Shea Moyner

                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

                  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

                    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]) = 'NON-PAR'

                    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] = 'NON-PAR' AND [In Network Code] = 'IN'

                    THEN 'PAR'

                    ELSEIF  [Par Status] = 'NON-PAR' AND [In Network Code] = 'OUT'

                    THEN 'NON-PAR' END

                     

                    Allowed2:

                    IF ATTR([ClaimType2]) = 'PAR'

                    THEN SUM([Allowed])

                    ELSEIF ATTR([ClaimType2]) = 'NON-PAR'

                    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]) = 'NON-PAR'

                    THEN WINDOW_SUM([Allowed2]) END

                    • 7. Re: Trying to calculate but the calculation is not summing the entire values
                      Bryce Larsen

                      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

                        Not sure I understand. Which IF statement?

                         

                        Your [Par Status] AND [In Network Code] are row-level 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

                          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 = non-par 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 non-par 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

                            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 row-level condition (the IIF part) inside an aggregation (the SUM part)

                            • 11. Re: Trying to calculate but the calculation is not summing the entire values
                              Bryce Larsen

                              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.

                              • 12. Re: Trying to calculate but the calculation is not summing the entire values
                                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] = 'NON-PAR' AND [In Network Code] = 'IN'

                                THEN 'PAR'

                                ELSEIF  [Par Status] = 'NON-PAR' AND [In Network Code] = 'OUT'

                                THEN 'NON-PAR' 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]) = 'NON-PAR'

                                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

                                  Good 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).

                                  1 of 1 people found this helpful