1 2 3 Previous Next 32 Replies Latest reply on Apr 17, 2017 2:08 PM by AnhVi Hoang

    Running Ratio

    AnhVi Hoang

      It's a very simple calculation, but I try different kind of formula but it still giving me the wrong answer.

      I use the Sample SuperStore data file, and limit the date to 2012

      Each table consist of 4 rows: 3 Product Category and the row Grand Total

      Each month will have 4 columns:

      a. Sum of Monthly Sales (Sales),

      b.Weighted % for the Monthly Sales (SaleWeight) = a / Total a

      c. A running sum YTD Sales of all previous months (RunSales)

      d. Weighted % for the RunSale => c/Total c

      The problem is in column 4 of each month that I annotate in the month of February

       

      Thank you very much,

       

      AnhVi Hoang

        • 1. Re: Running Ratio
          Jim Dehner

          hi

           

          Try this formula for your running sale weight            [RunSales]/WINDOW_SUM([RunSales])

           

          you should get this

           

          let me know if that helped

          Jim

          3 of 3 people found this helpful
          • 2. Re: Running Ratio
            AnhVi Hoang

            Jim, thank you very much, I highly appreciate your help.

            it worked perfectly.

            However, when I applied to my work that is a little more complex, it doesn't come out right (Item #3). I attached the Tableau file. The green tab worksheets work fine, the red tab has problem that I put on the corresponding worksheet title.

             

            1.        The BinSummary-NWork the name needs to be taken out and the count of faculty in each Bin (80+, 75%-80%,50%-75% and Below 50%
            2.        Same issue as one but with Column Dimension Bi-Weekly
            3.      Running Report: The Running Sum came out right but the MEET/Total (notMEET/Total) is NOT a RUNNING ratio but it’s the BI-Weekly period ratio instead

                           I have 3 different versions of Run_Ratio

                                a. Run_Ratio_V0:  [Run_Sum]/WINDOW_SUM([Run_Sum]) gives me something really wrong

                                b. Run_Ratio_V1: [CountofAchievement]/WINDOW_SUM([CountofAchievement]) gives Bi-Weekly Ratio instead of Running to date ratio

                                c. Run_Ratio_V3: [AccessionCountD]/WINDOW_SUM([AccessionCountD]) same issue gives Bi-Weekly Ratio instead of Running to date ratio

                4.    Need to synchronize the “scrolling” of the 3 worksheets in the dashboard, so no redundant FIN_DR_ID in each worksheet.

            1 of 1 people found this helpful
            • 3. Re: Running Ratio
              Jim Dehner

              I cant open the file - it has a tie back to SQL

              Jim

              1 of 1 people found this helpful
              • 4. Re: Running Ratio
                AnhVi Hoang

                I highly appreciate your help. I upload a twbx file, it supposes to be independent by itself, but I don't know why it asked for the tie back sql.

                I reload the file with the tde, hope you can open and help me with.

                 

                Thank you very much,

                 

                AnlhVi Hoang

                • 5. Re: Running Ratio
                  Jim Dehner

                  Good morning

                   

                  I worked the last day on getting the formula to calculate the bi weekly % of each FIN DR ID meet or non meet is to the total meet or non meet -

                  I think that is what you wanted in your point 3 above - This is just a brute force method - and somehow there has to be an easier way -

                   

                  That said the approach was to determine the total number of "MEETS" and the total "NON-MEETS" by bi-weekly period

                  Once that was found I divide then number of each FIN DIV ID total meets and non-meets by the grand total

                   

                  Sound simple enough but getting the bi-weekly grand total of Meet and Non-Meets was difficult

                   

                  For the MEETS the formula is shown below: (Note the formula for non-meets is the same except in line put in 'NON-MEET'

                   

                  Note these are not running totals and need to be aggregated in the percentage calculation

                   

                  if [MEET Target]='MEET' then

                  { EXCLUDE [Fin Dr Id]:

                  sum({ FIXED [MEET Target],[Bi Weekly],[Fin Dr Id]:

                  sum({ INCLUDE [Fin Dr Id]:([AccessionCountD])})})}

                  else 0 end

                   

                  The formula for the Biweekly % then becomes

                   

                  if attr([MEET Target])= 'MEET' then ([AccessionCountD])/avg([Bi-weekly total MEETS])

                  elseif

                  attr([MEET Target])='NOT MEET' then ([AccessionCountD])/avg([Bi-weekly Total Not Meets])

                  else 0 END

                   

                  See the Attached file the "correct bi-weekly % tab" note I included both a twb and a twbx file

                   

                  I stopped at this point to see if this meets your need

                  Jim

                  1 of 1 people found this helpful
                  • 6. Re: Running Ratio
                    AnhVi Hoang

                    Thank you very much Jim.

                     

                    I am following your instruction step by step because it's quite complicated for my level , especially the nested LOD.

                    Will let you know how once I'm done with it.

                    Can you help me with the other issue too?

                     

                    I highly appreciate your time and your support.

                     

                    AnhVi Hoang

                    1 of 1 people found this helpful
                    • 7. Re: Running Ratio
                      Jim Dehner

                      It was complicated form also -

                      The difficult part was getting the Total Meet or Not Meet for the 2 week period - I kept getting a number that did not match the running total but I could not figure out why

                       

                      Jim

                      1 of 1 people found this helpful
                      • 8. Re: Running Ratio
                        AnhVi Hoang

                        I spent half a day to decipher your instruction, and learn a lot of new things about LOD. I highly appreciate your help.

                        Would you please help me with 1, 2, and 4.

                        Although it's very closed but it's not what I'm expecting. I am not looking for the ratio of Meet/NonMeet, but just the Weight of Meet and the weight of Non Meet for each Dr. that would add up to 100%.

                        The problem is in the running_sum.

                        What I am looking for is simpler: The % Running Sum (% R_Cnt) for Week 3-4 is the R_Cnt/ TOTAL R_Cnt

                           

                        Week 1-2Week 3-4
                        FacultyTargetCount%R_Cnt% R_CntCount%R_Cnt% R_Cnt
                        Dr. AMeet5091%5091%2545%7593%
                        Non-Meet59%59%12%67%
                        TOTAL55100%55100%2647%81100%
                        • 9. Re: Running Ratio
                          Jim Dehner

                          I'm confused on what you are looking for - I thought your question was only with question #3 and the the sheets leading up to that were working for you?

                           

                          Can you send an excel sheet with the calculations and the results you expect - it would help clarify the calculation

                           

                          BTW getting the LOD expression to determine the % of Total MEETS and % of Total Non-MEETs - per doctor was the difficult part and that is why the LOD experssion was so complex

                           

                          Jim

                          1 of 1 people found this helpful
                          • 10. Re: Running Ratio
                            AnhVi Hoang

                            Thank you for your time, Jim.

                            I need help on all 4 questions that I haven't been able to solve any of them.

                            For question 3, I attach an simplify worksheet for just 2 Bi-Weekly period so you can see what I am looking for through the formula.

                            Sorry for my ambiguity in asking question.

                             

                            Once more, thank for your time.

                             

                            Anh-Vi

                            • 11. Re: Running Ratio
                              Jim Dehner

                              Good morning - I would like to do this in pieces - see the attached - sheet 'new sheet 4 4"

                               

                              I think this reproduces the excel sheet you sent - just want to confirm with you

                               

                              Jim

                              • 12. Re: Running Ratio
                                AnhVi Hoang

                                Jim,

                                 

                                Thank you so much for your time, Jim.

                                However there is a little bit of discrepancy, but I fix it but don't understand why it work.

                                The the table calculation you use: Running  Calculation Sum Previous 2 next 0 that make the % of Running Calculation right only for the 3 first periods (1-2,3-4,5-6), and starting with error on (7-8,9-10) see attached Excel sheet with error in comment (I just do calculation for the first 2 Dr_ID)

                                So I just make an "educational guess" and change the Sum Previous to 3, then 4, then 5, etc. It gradually works fine; so I keep it at 11 (guessing enough for 12 month of a year)?

                                Please enlighten me and if possible give me some referring learnings article how those Table Calculation work.

                                 

                                Thus, I think issue #3 can be closed. Can you go on helping me with issue 1,2 and 4.

                                 

                                Thank you very much,

                                 

                                Anh-Vi

                                • 13. Re: Running Ratio
                                  Jim Dehner

                                  Let me see what I can do on those (1,2,4)

                                   

                                  If you hover over the green pill for the table calculation it walks you through the specifics -

                                  In general table calculations are Tableau's way of looking at a matrix (similar to excel) and performing column to column and row to row calculations.

                                  You have used them to create running totals - % of total - % of rows and the like -

                                  The moving calculation allowed us to put in 2 different calculations - the first moving was summing over periods (and yes you are right it should have included the previous 11 periods "IF THEY ARE AVAILABLE" - The second part of the calculation is just a % of total calculation

                                  Now there are 2 really cool things you can do with table calculation - the First is you can drag the pill with the table calculation off the detail mark and into the measures tray and it will pop up as a Calculation n and still be on the marks card but now as the same Calculation n - in the measure's field you can Edit (right click) the calculation and the formula will show up - I use that a lot to copy the formula and use it in a different formula - either as part of the formula or give it a separate name and use that named field in a calculation. 

                                  The other thing you can do is seen in the shot below

                                  in the Edit frame that opens for the table calculation - in the lower right corner you will see "Default Table Calculation"

                                  When you open that up the Advanced table calc editor opens and you can change and add fields to the calculation -

                                  If you go to the Drop down and scroll all the way to the bottom the 3rd pop up on the right opens - there you can select the fields (path) you want the calculation to follow - select the order and how the sort will be done (can be a calculation) - think  here about the hierarchy you created in your vis - the default sequence for the calculation is left to right - here you can change that

                                   

                                  - then back on the center pop up you can set the the Restarting point.

                                   

                                  JIm

                                   

                                   

                                   

                                  • 14. Re: Running Ratio
                                    AnhVi Hoang

                                    Jim,

                                    Thank you for such wonderful explanation. It less than a week that you were helping me, I have learn much more than 3 month I tried to decipher Table Calculation and LOD by myself and through trial and error.

                                    When I modified it SumPrevious to 11 it becomes WINDOW_SUM([AccessionCountD], -11, 0), thus explaining it's 11 periods that I luckily modified and it worked

                                     

                                    I'm looking forward for your help in 1, 2, and 4.

                                    Cheers,

                                     

                                    AnhVi

                                    1 2 3 Previous Next