12 Replies Latest reply on Aug 12, 2016 12:55 PM by Tom W

    Calculation help!

    Sheila Pearson


      This doesn't seem that it would be that difficult, but I'm having a hard time figuring it out. I have a data source that contains policy information filed through two systems (FSL and CH), which is a dimension in my workbook. Each row of data is associated with either FSL or CH. What I'm trying to do determine if there are any "Insureds" (another field in my data source) that were filed last year in one system (CH) and this year in the other (FSL). I tried creating two sets, one for CH and one for FSL, and then creating a combined set of like members but I can't do it based on a particular field.

       

      Any ideas??

       

      Thanks!

       

      Sheila

        • 1. Re: Calculation help!
          Tom W

          Hi Pearson,

          It's pretty difficult to help without seeing some sample data. Can you please upload a Tableau Packaged Workbook?

           

          Thanks!

          • 2. Re: Calculation help!
            Benjamin Greene

            Tom is right. Hard to make a guess without seeing the data. But I'll make a guess anyway.

             

            IF [FSL or CH Dimension]="CH"

            AND [Other Dimension]="Insureds"

            AND YEAR([Filing Date])=2015

            THEN 1

            ELSEIF

            [FSL or CH Dimension]="FSL"

            AND [Other Dimension]="Insureds"

            AND YEAR([Filing Date])=2016

            THEN 1

            ELSE 0

            END

            • 3. Re: Calculation help!
              Sheila Pearson


              Not sure if I did this correctly, but I've attached the workbook.

              • 4. Re: Calculation help!
                Sheila Pearson

                Not sure this one will work because I'm looking for like Insureds filed in one system during 2015 and the other in 2016...I don't have a particular Insured, just looking for if there are any that overlap between the two systems. I attached the workbook to Tom's post. Thanks!

                • 5. Re: Calculation help!
                  Rahul Upadhye

                  When comparing multiple sets across a different dimension (here date) you have to add those sets to Context.

                  In ur filter shelf use Add to Context

                  • 6. Re: Calculation help!
                    Tom W

                    Pearson,

                    The workbook you've attached has like 20 sheets. Can you please let us know which sheet you're working on - what are the names of the fields we need to be looking at here? In situations like this, it's always easier for us if you prepare a trimmed down example of your workbook with a sheet which includes only the dimensions / measures you're referencing in your post.

                    • 7. Re: Calculation help!
                      Sheila Pearson

                      It’s blank, but I was working on the last sheet in the workbook, sheet 46. I need to see the following….

                       

                      Any “Insured” filed during 2015 (which would be “Transaction Received Date”) that is identified with “CH Florida Only” in the CH/FSL group that was subsequently filed during 2016 identified with FSL in the CH/FSL group.

                       

                      Just don’t know how to write a formula that would give me that info.

                       

                      Thanks!

                       

                      Sheila

                      Florida Surplus Lines Service Office

                      800-562-4496 ext 125

                      • 8. Re: Calculation help!
                        Tom W

                        Which source am I meant to be using? FSL AND CH Data or Sheet 1? The FSL source doesn't work because it's not setup as an extracted source. You would need to extract it and re-upload.

                         

                        The Sheet 1 source contains no data for 2015 in the Transaction Received Date dimension.

                         

                        Given the above, can you please re-attach a more contained example with the correct source and the fields in the view? Thanks!

                        • 9. Re: Calculation help!
                          Sheila Pearson

                          ok, let's try this again. I've attached a clean version, with an extract of the 2015-2016 data. Hopefully, this will work.

                          • 10. Re: Calculation help!
                            Tom W

                            A little tip for future threads, you should limit your extract to a subset of your data or X number of rows. 32MB files will scare some people off. Not me though!

                             

                            Are you sure you have data in this workbook to suit the scenario you're asking for?

                            If I drag 'Insured' on to the row shelf and then add this calculation, I don't see any individual 'Insured' that meets the criteria of having a 2015 CH Florida Only and a 2016 FSL.

                             

                            Here's the calc I used;

                             

                            IF

                            (

                                count(if year([Transaction Received Date])=2015 then [Transaction Received Date] end)>0 and

                                attr([Ch/Fsl (group)])="CH Florida Only"

                            )

                            and

                            (

                            count(if year([Transaction Received Date])=2016 then [Transaction Received Date] end)>0 and

                            attr([Ch/Fsl (group)])="FSL"

                            )

                             

                            then 'Meets Criteria'

                            else ''

                            end

                            • 11. Re: Calculation help!
                              Sheila Pearson

                              That’s what I’m trying to determine is if there are any in the data. However, how are you “adding” the calculation, just “Add to Sheet”? That doesn’t seem to only show the Insureds in both as I still have everything listed if I drag Insured to Rows.

                               

                              I have the Transaction Received Date as a filter only selecting 2015 and 2016, and also CH/FSL only selecting FSL and CH Florida Only. I have a copy of Year on Columns with the CH/FSL (group) and then Insured on Rows. The calculation is on color?

                               

                              Thanks!

                               

                              Sheila

                              Florida Surplus Lines Service Office

                              800-562-4496 ext 125

                              • 12. Re: Calculation help!
                                Tom W

                                I created the above as a calculated field first, then dragged that into the rows shelf along with the 'Insured' dimension. You can then use it as a filter, but I found that it never returns the 'meets criteria'.

                                 

                                I think you best next step would actually be to break the calculated field down into two parts so you can assess the CH Florida Only as;

                                IF

                                    count(if year([Transaction Received Date])=2015 then [Transaction Received Date] end)>0 and

                                    attr([Ch/Fsl (group)])="CH Florida Only"

                                THEN 'CH Florida Only 2015'

                                ELSE ''

                                END

                                 

                                 

                                and a second formula for the FSL 2016;

                                IF count(if year([Transaction Received Date])=2016 then [Transaction Received Date] end)>0 and

                                attr([Ch/Fsl (group)])="FSL"

                                THEN 'FSL 2016'

                                ELSE ''

                                END