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

# Running Ratio

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

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

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

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

Jim

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

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

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

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?

AnhVi Hoang

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

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

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

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-2 Week 3-4 Faculty Target Count % R_Cnt % R_Cnt Count % R_Cnt % R_Cnt Dr. A Meet 50 91% 50 91% 25 45% 75 93% Non-Meet 5 9% 5 9% 1 2% 6 7% TOTAL 55 100% 55 100% 26 47% 81 100%
• ###### 9. Re: Running Ratio

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

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

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

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

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

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