1 2 Previous Next 17 Replies Latest reply on Jul 4, 2017 12:54 PM by Saket Metkari

# Calculations - Using trends

Hi Team,

I have a scenario -

I have to create trends for Billable Time, Warranty, Training and Travel using Months or Quarters (All into Percentages)

The problem is calculations -

Workbook - Trends (Billable)

I have created a sheet here i want to see trends per Month into percentages (According to the Service Managers)

It should show trends - only for Billable (So i tried to hide all other fields in Work Type) (I cannot deselect other field or it will take 100% for Billable)

So, it shows me - 61% total, so when I want to see by Month it shows me different % (Which is wrong) (Here I don't want to filter I have to show trends )

It should show me - Jan (55%), Feb (66%) and Mar (66%) and April (57%) and so on,  in this way I can see trends for each Month into Percentages.

Thanks !
SAKET

• ###### 1. Re: Calculations - Using trends

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.

• ###### 2. Re: Calculations - Using trends

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

• ###### 3. Re: Calculations - Using trends

Hey Jim, For more reference please find excel sheet.

Thanks !

SAKET

• ###### 4. Re: Calculations - Using trends

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

• ###### 5. Re: Calculations - Using trends

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

• ###### 6. Re: Calculations - Using trends

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

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.

1 of 1 people found this helpful
• ###### 7. Re: Calculations - Using trends

Jim,

This helped a lot, thanks

And thanks for the links, I will surely go through it.

Thanks !

SAKET

• ###### 8. Re: Calculations - Using trends

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

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.

Thanks !

SAKET

• ###### 10. Re: Calculations - Using trends

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

• ###### 11. Re: Calculations - Using trends

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

• ###### 12. Re: Calculations - Using trends

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 day-month-year format (is that a standard in europe?) and that did not work with my default of month-day-year

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

• ###### 13. Re: Calculations - Using trends

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

• ###### 14. Re: Calculations - Using trends

I believe this is the solution I sent you earlier

where the count is = Countd([Sso Id])

1 2 Previous Next