
1. Re: Calculations  Using trends
Jim Dehner Jul 1, 2017 12:52 PM (in response to Saket Metkari)Hi
there are 2 ways to go  you can hard code the "Billable" into the formula with
(If attr([Work Type])="BILLABLE" then sum([Hours]) end)/sum({ FIXED (DATEPART('year', [Date])*100 + DATEPART('month', [Date])):sum([Hours])})
Or you can fix the calculation by work type and then filter for Billable with
({ INCLUDE [Work Type]: sum([Hours])})/({ FIXED (DATEPART('year', [Date])*100 + DATEPART('month', [Date])):sum([Hours])})
The choice is based on what else you need  either way the results are
Let me know if this helps
Jim
If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution. Thank you.

Book7 jd.twbx 127.1 KB


2. Re: Calculations  Using trends
Saket Metkari Jul 1, 2017 7:58 PM (in response to Saket Metkari)Hi Jim,
This helps a lot, (Just one more thing i have to show this percentages via Service Managers(They are from different regions))
When I filter via Service Managers (Please see Screenshot)  Then it doesn't give proper results
So for instance  let' say
Aaron (UK)  Jan  32%, Feb  56%, Mar  48%
Frederic(SWE)  Jan  67%, Feb  79, Mar  87%
It should give me these results
And so on.
Really appreciate your help. thank you so much !
thanks a lot !
SAKET
Capture.PNG 59.3 KB


3. Re: Calculations  Using trends
Saket Metkari Jul 1, 2017 7:58 PM (in response to Saket Metkari)Hey Jim, For more reference please find excel sheet.
Thanks !
SAKET

Book1.xlsx 142.2 KB


4. Re: Calculations  Using trends
Jim Dehner Jul 2, 2017 5:58 AM (in response to Saket Metkari)Hi
Tokk a little bit to understand the calculation that you were trying to accomplish  the last question is for any given sm what is his % of billable to the total of all his hours.
The first calculation I gave you is the overall percent of billable hours to the total of all hours  in each case by month
That will change the denominator of the formula  the formula now becomes
({ INCLUDE [Work Type]: sum([Hours])})/({ FIXED [Service Managers],(DATEPART('year', [Date])*100 + DATEPART('month', [Date])):sum([Hours])})
and your results become
Think of LOD functions as creating partitions and permutations for the dimensions that precede the colon :
In this case in words the denominator would read  for each service manager and for each year/month sum the hours
Let me know if this helps
Jim

Book7 jd (1).twbx 198.7 KB


5. Re: Calculations  Using trends
Saket Metkari Jul 2, 2017 7:37 AM (in response to Jim Dehner)Jim,
Thanks a ton, thank u very very much , it certainly helps a lot !
The thing is I am new to tableau and to be honest not good with LOD expressions as of now. (But trying to cope with this kind of calculations)
Jim, Just one more thing please,
For trends of Travel hours 
I have to filter Operational / Activity (Code 17  As it represents travel Hours)
Formula : travel Hours / total Hours of that particular region * 100
For Instance  (Attached Sheet)
Aaron  Jan (76.5 / 904 * 100) = 8.5 %
Feb (161 / 820,75 * 100) = 19.6 %
Juergen  Jan (417.55 / 2143) = 19.4 %
And So on..
For warranty Trends 
Just one thing, In work Type  Like same as Billable, for Warranty  I have to add Product Warranty + Service Warranty and then show into Percentages same as above.
The formula I used, same as in the sheet just added Product warranty + Service Warranty (But didn't work)
Can you help here !
Sorry to bother you, but I will learn this as soon as possible.
Thanks !
SAKET

Book1.xlsx 146.3 KB


6. Re: Calculations  Using trends
Jim Dehner Jul 2, 2017 8:13 AM (in response to Saket Metkari)Hi
first to the solution
I have changed the LOD expression to be a ore general solution that will respond to your filter request  you should be able to drop a dimension filter on the shelf and the combine the selections to get the total you want
the formula is
sum(({ INCLUDE [Work Type]: sum([Hours])}))/sum(({ FIXED [Service Managers],(DATEPART('year', [Date])*100 + DATEPART('month', [Date])):sum([Hours])}))
when you use this with the travel code you referenced the viz will become
Note that the value that you reference for Feb in you response is different  I believe you crossed the data for Fredric with that for Arron
see the table below
Note also that the % of hours now sum
see the purple tab
Now here are some links that cover LOD expressions
Overview: Level of Detail Expressions
Understanding Level of Detail (LOD) Expressions  Tableau Software
Top 15 LOD Expressions  Tableau Software
Let me know if this helps
Jim
If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution. Thank you.

Book7 jd (1).twbx 203.7 KB


7. Re: Calculations  Using trends
Saket Metkari Jul 2, 2017 8:48 AM (in response to Jim Dehner)Jim,
This helped a lot, thanks
And thanks for the links, I will surely go through it.
Thanks !
SAKET

8. Re: Calculations  Using trends
Jim Dehner Jul 2, 2017 8:52 AM (in response to Saket Metkari)Lod expression take practice. Don't be discouraged. You'll get there
Jim
On Jul 2, 2017 10:48 AM, "Saket Metkari" <tableaucommunity@tableau.com>

9. Re: Calculations  Using trends
Saket Metkari Jul 4, 2017 9:12 AM (in response to Jim Dehner)Hi Jim,
Sorry to bother you again, but I have another problem regarding different scenario.
As you understand my problems very well, need your help.
1) Please find attached Excel  Dashboard, I need to create these dashboards
The problem is dates (I need to create 2 parameters  Start Date & End Date)
When I select Sep 17 in start date parameter and Aug 18 date in End Date Parameter, it should display Employee Details between these period.
2) I need to show trends of Head Counts using Product Lines and Positions on a Monthly Basis.
Like Month by Month how the count is increasing.
Problems
When I create pivot of all date columns the number of rows gets increased and my head count is wrong.
For your more understanding I am sharing you an excel file(dashboard which I have to prepare), the source file and tableau workbook.
Thanks your for your help and understanding.
Need your help here please.
Thanks !
SAKET

Dashboard.xlsx 49.5 KB

Book11.twbx 161.5 KB

Book1.xlsx 306.9 KB


10. Re: Calculations  Using trends
Jim Dehner Jul 4, 2017 9:28 AM (in response to Saket Metkari)Hi
I cant tell look at your data what you want to to count but I think this is it Countd([Sso Id])  where SSo ID is the unique identifier that you want to count  the Countd just say only count each ID once 
I made this text chart  it is under the see this sheet tab
Jim

Book11.twbx 167.8 KB


11. Re: Calculations  Using trends
Saket Metkari Jul 4, 2017 9:47 AM (in response to Jim Dehner)Hi Jim,
When I do this in pivot, then I can get this calculations (Attached Screenshot)
Can you please help me with the date problem ?
Thanks !
SAKET

Capture.PNG 17.3 KB


12. Re: Calculations  Using trends
Jim Dehner Jul 4, 2017 11:05 AM (in response to Saket Metkari)Ok here is the issue  your data set brought the date field (now called pivot field names) in as a String field (text) not a date field
Typically I would just click on the icon next to the name and convert it to a date  but your field is in a daymonthyear format (is that a standard in europe?) and that did not work with my default of monthdayyear
I did a "Brute Force" approach using the Date function
date(Mid([Pivot Field Names],4,3)+""+left([Pivot Field Names],2)+""+"20"+Right([Pivot Field Names],2))
This function just takes the string apart and make it month  day year  and then convert it to a date
it is called new date on the attached and it is what you can use in your viz  it will work as a date calendar  a better solution would be to try to click on the icon next to the name Pivot Field Name in your left data frame and select Date and see if it converts the way you want (your defaults may be different than mine)
Jim

Book11.twbx 180.8 KB


13. Re: Calculations  Using trends
Saket Metkari Jul 4, 2017 11:54 AM (in response to Jim Dehner)Thanks a lot, this helps !
Now, I have to calculate Count of employess according to positions each month.
here I have a problem, if you check screenshot it gives me wrong count.
Thanks !
SAKET

Capture.PNG 55.8 KB

Book11 (1).twbx 203.0 KB

