
1. Re: Running Ratio
Jim Dehner Mar 28, 2017 3:25 PM (in response to AnhVi Hoang)hi
Try this formula for your running sale weight [RunSales]/WINDOW_SUM([RunSales])
you should get this
let me know if that helped
Jim

RunningRatio.twbx 1.5 MB


2. Re: Running Ratio
AnhVi Hoang Mar 28, 2017 8:00 PM (in response to Jim Dehner)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.
 The BinSummaryNWork the name needs to be taken out and the count of faculty in each Bin (80+, 75%80%,50%75% and Below 50%
 Same issue as one but with Column Dimension BiWeekly
 Running Report: The Running Sum came out right but the MEET/Total (notMEET/Total) is NOT a RUNNING ratio but it’s the BIWeekly 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 BiWeekly Ratio instead of Running to date ratio
c. Run_Ratio_V3: [AccessionCountD]/WINDOW_SUM([AccessionCountD]) same issue gives BiWeekly 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.

ForHelpPurpose.twbx 4.7 MB

3. Re: Running Ratio
Jim Dehner Mar 29, 2017 6:00 AM (in response to AnhVi Hoang)1 of 1 people found this helpfulI cant open the file  it has a tie back to SQL
Jim

4. Re: Running Ratio
AnhVi Hoang Mar 29, 2017 9:26 AM (in response to Jim Dehner)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

ForHelpPurpose.twb 355.5 KB

BareMinimal.tde.zip 3.9 MB


5. Re: Running Ratio
Jim Dehner Mar 30, 2017 8:04 AM (in response to AnhVi Hoang)1 of 1 people found this helpfulGood 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 "NONMEETS" by biweekly period
Once that was found I divide then number of each FIN DIV ID total meets and nonmeets by the grand total
Sound simple enough but getting the biweekly grand total of Meet and NonMeets was difficult
For the MEETS the formula is shown below: (Note the formula for nonmeets is the same except in line put in 'NONMEET'
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([Biweekly total MEETS])
elseif
attr([MEET Target])='NOT MEET' then ([AccessionCountD])/avg([Biweekly Total Not Meets])
else 0 END
See the Attached file the "correct biweekly % tab" note I included both a twb and a twbx file
I stopped at this point to see if this meets your need
Jim

6. Re: Running Ratio
AnhVi Hoang Mar 30, 2017 10:06 AM (in response to Jim Dehner)1 of 1 people found this helpfulThank 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

7. Re: Running Ratio
Jim Dehner Mar 30, 2017 10:25 AM (in response to AnhVi Hoang)1 of 1 people found this helpfulIt 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

8. Re: Running Ratio
AnhVi Hoang Apr 3, 2017 1:29 PM (in response to Jim Dehner)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 34 is the R_Cnt/ TOTAL R_Cnt
Week 12 Week 34 Faculty Target Count % R_Cnt % R_Cnt Count % R_Cnt % R_Cnt Dr. A Meet 50 91% 50 91% 25 45% 75 93% NonMeet 5 9% 5 9% 1 2% 6 7% TOTAL 55 100% 55 100% 26 47% 81 100% 
9. Re: Running Ratio
Jim Dehner Apr 3, 2017 2:14 PM (in response to AnhVi Hoang)1 of 1 people found this helpfulI'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 NonMEETs  per doctor was the difficult part and that is why the LOD experssion was so complex
Jim

10. Re: Running Ratio
AnhVi Hoang Apr 3, 2017 4:14 PM (in response to Jim Dehner)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 BiWeekly 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.
AnhVi

Sample.xlsx 9.6 KB


11. Re: Running Ratio
Jim Dehner Apr 4, 2017 6:32 AM (in response to AnhVi Hoang)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 Apr 4, 2017 9:46 AM (in response to Jim Dehner)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 (12,34,56), and starting with error on (78,910) 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,
AnhVi

Table Calculation Snip.PNG 45.5 KB

SamplewError.xlsx 14.4 KB


13. Re: Running Ratio
Jim Dehner Apr 4, 2017 11:20 AM (in response to AnhVi Hoang)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 Apr 4, 2017 1:06 PM (in response to Jim Dehner)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