1 2 Previous Next 18 Replies Latest reply on Jul 13, 2017 4:48 AM by Jim Dehner

    Summing Instances According to Filter

    Madelyn Hutton

      Hi all,

       

      I've just started to dabble in Tableau to work on a project for my internship and I could use some help. I'm trying to create a stack line graph (which I've come to realize may not be possible for my situation, read on...) with R/1000 (repairs per thousand vehicles) on the y-axis and vehicle build date on the x-axis to display warranty claim data. The end user should be able to select the warranty claim topic, a range of dates, and "'__ months in service" (MIS) to show the R/1000 trend depending on the vehicle build date. I'm running into some issues trying to calculate that R/1000 measure.

       

      My numerator: cumulative number of claims up to selected MIS (if looking at 3 MIS, select all claims at MIS <= 3)

      My denominator: total # of vehicles with at least selected MIS (if looking at 3 MIS, select all vehicles with MIS > 3)

      Dividing these two and multiplying by a thousand is what I want for R/1000.

       

      I don't want the end user to have to manually select those properties from each field, so I've started working on calculating that numerator based on a MIS parameter. I first created a calculated field to calculate MIS at the time the claim was filed.

       

      This is what I have:

       

      CASE [Parameters].[MIS]

          WHEN 1 THEN SUM(IIF([claim_MIS]<=1,1,0)

          WHEN 2 THEN SUM(IIF([claim_MIS]<=2,1,0)

          WHEN 3 THEN SUM(IIF([claim_MIS]<=3,1,0)

      ... etc

      END

       

      This works to an extent, unless the vehicle has had multiple claim topics recorded at the same mileage. So, for example, even if I set my parameter to 3 MIS and my topic filter to "NOx Sensor", if a vehicle had a NOx Sensor, Lube, and Oil issue at 3 MIS, my calculated field for that vehicle is returning "3" instead of "1." I'm not sure why it's even counting those instances if the filter is set...

       

      Like I said, I'm new to Tableau, so I apologize if I'm struggling to describe my issue. I can't upload my workbook due to company confidentiality, but will be prompt to respond to any questions.

        • 1. Re: Summing Instances According to Filter
          Jim Dehner

          Hi Madelyn -

           

          I think I understand your question - it would be easier to work on if you would attach a workbook in a TWBX format - could be some dummy data if you are concerned about confidentiality

           

          That said - If I understand correctly you have a parameter (say MIS Parameter) and you want to count the total number of CLAIMS that vehicles with equal to or less than that parameter in MIS terms - and compare that the total number of VEHICLES that have been in service at least that long -

           

          The Numerator calculation is just      If [MIS] <= [Mis parameter] then  countd(claim number]) end           where claim number is whatever you use to identify claims

          The denominator is          {Fixed: sum( If[MIS]>=[MIS Parameter] then countd(Vehicle ID) end )}          Where Fixed is an LOD expression to lock in the value to use in your other calculations

           

          Then you will need to do a division - Numerator over denominator - may need to do some aggregation based on how your data is structured

           

          Let me know if that helped

          Jim

          • 2. Re: Summing Instances According to Filter
            Madelyn Hutton

            Hi Jim,

             

            Thanks for your prompt reply! Working on creating some dummy data right now, but I just tried your numerator fix and received the error: "Cannot mix aggregate and non-aggregate comparisons or results in 'IF' expressions" here is my code

             

            IF [claimm_MIS] <= [Parameters].[MIS]

                THEN COUNTD([claim topic])

            END

             

            I'm also not sure if my denominator needs fixing as it will change depending on the parameter chosen.

            • 3. Re: Summing Instances According to Filter
              Jim Dehner

              Sorry

              That 's why I usually test the response before I send it but I don't have a data set to simulate yours

              OK - let me explain what the message means - once you use an aggregate form in a formula all the other elements must be aggregated also

              the Countd is an aggregate - so the claimn_MIS needs to be aggregated - you can do that with a ATTR([claimm_MIS])

               

              Its a little confusing  but Attr() and Min or Max can be used to "aggregate" stand alone dimensions

               

              Jim

              1 of 1 people found this helpful
              • 4. Re: Summing Instances According to Filter
                Madelyn Hutton

                Hi Jim,

                 

                Even after changing to ATTR(), I recieved "Null" for all build date entries. Here is the dummy TWBX, hopefully it will help.

                 

                Thanks

                • 5. Re: Summing Instances According to Filter
                  Jim Dehner

                  I don't see the dummy file -

                   

                  Did you click the Advanced editor and add an attachment button at the top right of the frame then attache  the file with the button at the lower left?

                   

                  Jim

                  • 6. Re: Summing Instances According to Filter
                    Madelyn Hutton

                    I pulled my signature move...here is the workbook.

                    • 7. Re: Summing Instances According to Filter
                      Jim Dehner

                      Hi

                      Hope you had a good 4th -

                      I think I understand your calculated fields - not sure at what level you want to calculate your % of claims - but the attached uses the denominator to be the total of all claims (MIS-pl2)

                       

                      the formula is      sum([ifstemnt])/sum({ FIXED :sum([claim_MIS_p2])})

                       

                      It will yield the 2 charts below -

                       

                       

                       

                       

                      Let me know if this is really what you want -

                      Jim

                      1 of 1 people found this helpful
                      • 8. Re: Summing Instances According to Filter
                        Madelyn Hutton

                        Hi Jim,

                         

                        Thanks so much for taking the time to help me out. I think we're on the right track, but still not quite what I need to do. I've attached an excel sheet with a image of the graph I was asked to recreate through a Tableau dashboard and some of the corresponding data from the graph. The graph was generated by our warranty software and the series on the right legend are 1, 3, 6, 9, and 12 MIS.

                         

                        As you can see, my numerator and denominator for the R/1000 calculation will need to be changed for each selected MIS, so I don't need a fixed count for the divisor. When I was recreating the graph on excel, I developed pivot tables for my claim and sales data to manually copy + paste the numbers. If looking at 6 MIS, on my claim pivot (numerator), I would have to select all MIS <= 6 on the pivot table filter and copy the corresponding values for each build month. Likewise, on my sales pivot (denominator), I would have to select all MIS > 6 and manually copy the values for each MIS series.

                         

                        I also noticed in the workbook sent back to me you were running into the same issues I was: for VINs that had 2 different failures at the same mileage, the if statement was returning values > 1 even if the noun was filtered. That numerator needs to be a count (not purely distinct because some VINs have multiple failures for the same noun at <= 1 MIS) of all selected noun claims filed at <= selected MIS.

                         

                        I'm sorry this is kind of difficult to explain, I really appreciate your help!

                        • 9. Re: Summing Instances According to Filter
                          Jim Dehner

                          Ok

                           

                          I think I understand that you want a single chart that will present the 3,6,9,and 12 month warranty data as a ratio of the number of incidents in the "Period" to the total number incidents by claim type (noun) that were out of period (i.e. the incident was recorded after the warranty period)  Not clear on the denominator - is that by claim type (Noun) or is it the total for all claim types?

                           

                          Please let me know and I will look at it

                          Jim

                          • 10. Re: Summing Instances According to Filter
                            Madelyn Hutton

                            The parameters for the graph series can be MIS from 1 up until 50, the 1, 3, 6, etc intervals were just randomly selected as an example from my mentor. This graph is only for one selected claim noun, but I'll need to be able to select which claim noun to look at in the final dashboard.

                             

                            The graph should read like this: for vehicles built in October 2014, of which have least 1 month in service, there were 130 repairs (on the selected noun) per 1000 vehicles. For vehicles built in October 2014 with at least 3 MIS, there were approximately 185 repairs per 1000 vehicles. For 6 MIS, approximately 232 repairs per 1000 vehicles, and so on...

                             

                            Whether the failure occurred in or outside of warranty is not relevant. As far as the numerator, we're just looking at MIS at time of failure/claim (based on warranty start date) from the claims table. For the denominator, we're only looking at overall MIS from today starting from the warranty start date from the sales table.

                             

                            The R/1000 measure normalizes that ratio. The numerator of the ratio is the cumulative number of claims based on selected noun. So, if I wanted R/1000 for Lube claims at 3 MIS, the numerator would be the total number of Lube claims that occurred at ...-2,-1,0,1,2,and 3 MIS. The denominator would be the total number of ALL vehicles (with or without selected noun claim) with 4, 5, 6, 7, 8, 9, 10... MIS.

                             

                            Hope this helps. It took me some time to get my head around that ratio as well.

                            • 11. Re: Summing Instances According to Filter
                              Jim Dehner

                              hi see the attached

                               

                              I haven't come back to the values you quoted - but getting close

                               

                              here is the "graph"

                               

                               

                              you will see that each time period is a separate calculation - 1,3,6,9,and 12 months

                              The Noun is a filter that you can use to pick and choose

                              The columns are your production dates (note I used the Fill in Missing Values to make the calendar continuous for all yr/months and placed it on the filter shelf

                               

                              I am basing the count for the numerator around the MIS_p2 field - I think that is right but not sure - in any event the 1 month MIS calculation is :

                               

                                        1000*(SUM(if  ([claim_MIS_p2])<=1 then 1 else 0 end)) / SUM({ FIXED :COUNTD(if ([claim_MIS_p2])>1 then [VIN] end)})

                               

                              the rest are similar

                               

                              Let me know if this helps

                              Jim

                              1 of 1 people found this helpful
                              • 12. Re: Summing Instances According to Filter
                                Madelyn Hutton

                                Hi Jim,

                                 

                                I was able to figure out the numerator using a distinct count of a unique attribute for each claim. Now on to the denominator....

                                 

                                To reiterate, I need a count of all the vehicles (via the sales table) with MIS > selected MIS with and without a claim (claim noun filter should not affect count). I still need to be able to filter the vehicles by model family (9a/10a). I'm unsure how/if the FIXED function can be used here.

                                • 13. Re: Summing Instances According to Filter
                                  Jim Dehner

                                  Give fixed and exclude a try. I'm tied up today and may not be able to get

                                  to it

                                   

                                  On Jul 6, 2017 8:54 AM, "Madelyn Hutton" <tableaucommunity@tableau.com>

                                  • 14. Re: Summing Instances According to Filter
                                    Madelyn Hutton

                                    Hi Jim,

                                     

                                    Finally cracked the code! I used {FIXED DATETRUNC('month',[veh build date]), [family]:COUNTD(IF [sales_MIS] > [MIS Parameter] THEN [vin]} and aggregated using SUM for the denominator. I can't get the stacked lines using parameters but it seems to be the best way to do it without having to create calculated fields for each series.

                                     

                                    Thanks for all of your help!

                                    1 2 Previous Next