1 2 Previous Next 24 Replies Latest reply on Sep 10, 2017 7:35 AM by Shinichiro Murakami

# LOD Expression

Hi Team,

I am new to tableau and need your help in LOD Expression.

IF you go to Components Dashboard -

Work Center - Labor Hours + Rework + Billable by Per week

For Ex - Week 28 ->                     656 + 9 + 223,4 = 888.4 ,   I need this for every week in one table.

Thanks !

Saket

• ###### 1. Re: LOD Expression

Hi

your data is ver complex and I am confused with your goal

you alread have a formula for billable hours as

SUM([Labor]) +

SUM([Raw Data (CAT2 Data)].[Total quantity]) +

SUM([Rework])

And you want a formula for      Labor + Rework + Billable Hours

it looks like you are double counting Labor and Rework

also on the surface there does not appear to be a reason that you would need and LOD expression - can you explain the need -

It will be an issue because all the data fields in must come from the same data source - your Billable Hour calculation above does not meet that criteria

Jim

Let me know if this helped

• ###### 2. Re: LOD Expression

Hi Jim,

Sorry for not being precise -

I have 3 sources -

Production-Components

Production-Systems

CAT2 Data

I have to create certain formulas and using it, create some charts.(Attached Screenshot)

FYI -

Production-Components

Production-System                                               CAT2 Data - half data is for Components and half is for Systems (Depending Upon the cost centers)

Now I will explain the formulas in detail -

Please consider only 2 sheets as of now -

CAT2 Billable Hours and Components dashboard -

Source -                        Production-Components + Production-Component + CAT2 Data

I need addition of ----> Work Center labor Hours + Rework + CAT2 billable Hours

For instance -

Week 28 -            656 + 9 + 7  = 672

The problem is addition of data from 2 different sources. (I need to show in chart on a weekly basis)

Thanks !

Saket

• ###### 3. Re: LOD Expression

Hi Jim,

My problem is this is not working -

you alread have a formula for billable hours as

SUM([Labor]) +

SUM([Raw Data (CAT2 Data)].[Total quantity]) +

SUM([Rework])

As in CAT2 my answer depends upon 2 filters -

Source Object and Order Type

if Order type is billable and source object - 6046502AV =  CAT2 Billable..

Billable Hours = labor + rework + CAT2 Billable

Thanks !

Saket

• ###### 4. Re: LOD Expression

I have not seen your workbook but if you are joining all your datasources then Yes You can use LOD but if you are blending then the aggregation would break.

Thanks

Deepak

• ###### 5. Re: LOD Expression

Hi Deepak,

Can you please have a look in the workbook and let me know, I am a bit confused.

Thanks a lot !

Saket

• ###### 6. Re: LOD Expression

SAket,

I looked into workbook. But I was pretty confused.

For example, I cannot see any number of 656,9  or 7

In week 28.

What is the key field you want to connect between all three data?

Emp vs Personnel ID

Team vs Team

Order type vs Billable

What is the relationship of Source object with other two sheets??   etcetcetc

Thanks,

Shin

• ###### 7. Re: LOD Expression

Hi Shin,

656 comes from source - Production-Components , please check screenshot. (Component Dashboard)

9 comes from source - Production-Components,please check screenshot. (Component Dashboard)

7 comes from CAT2 source - Screenshot- Week 28 (Here it depends upon filter - Source Object - 6046502AV and Order Type - Billable)

Formula for Billable Hours is - Work Center labor Hours + Rework + CAT2 Billable

I have attached all 3 excel sheets, sorry I am not much familiar with tableau.

I want to connect with Employee Name and Name.

Thanks you for your support and help !

Saket

• ###### 8. Re: LOD Expression

Saket,

You confused us/yourself with below relationships.

First two sheets has two "week"s , one is based on real date and other is based on something different..

The sheet CAT 2 also has field of "week' but it's different from "week' of real date which your individual table shows.

To get 672 on Week 28, you need to create another field like Week ID based on real date and link..

On both sheets of component and System

Link CAT 2 with other two sheets only with Week And Week ID.

If I link with Name and Employee Name as you said, the number becomes different than 672.

Thanks,

Shin

1 of 1 people found this helpful
• ###### 9. Re: LOD Expression

Hi Shin,

Thank you so much, this helps a lot. And sorry for the confusion.

Please check dashboards marked with Red.

Hours not booked = SC Available Hours - (Billable + Non-Billable + Sickness)    Ex - Week 28 -    1114 - (672+97+32) = 313

Actual Utilization ratio = Billable Hours / SC Available Hours                          Ex - Week 28 - 672/1114 * 100= 60.32%

Non-Billable-Not Booked = Non-Billable + Hours not booked / SC Available Hours    Ex - Week 28 - 94+313/1114 * 100 =36.5%

Sickness = sickness hours / SC Actuals              Ex - 32 / 1114 * 100 = 2.87%

And with certain graphs.(Word File)

Thanks !

Saket

• ###### 10. Re: LOD Expression

Saket,

Request is too much with very poor information with very tight schedule.

Please clarify very in detail "Each word" is representing which field of which data source"

Even with that , I am no sure I or someone really can meat your tight schedule request,.

Thanks,

Shin

• ###### 11. Re: LOD Expression

HI Shin,

Apologies.

I want to create calculated fields for these formulas - (Please see attached screenshot)

I have this sheet in excel and want to achieve same results in tableau. I have sheet for every functionality for instance Billable, Sickness, Non-BIllable.

Using these results I have to create calculated fields. (Attached Excel pwd - Wun01)

Hours not booked = SC Available Hours - (Billable + Non-Billable + Sickness)    Ex - Week 28 -    1114 - (672+97+32) = 313

Actual Utilization ratio = Billable Hours / SC Available Hours                          Ex - Week 28 - 672/1114 * 100= 60.32%

Non-Billable-Not Booked = Non-Billable + Hours not booked / SC Available Hours    Ex - Week 28 - 94+313/1114 * 100 =36.5%

Sickness = sickness hours / SC Actuals              Ex - 32 / 1114 * 100 = 2.87%

Thanks !

Saket

• ###### 12. Re: LOD Expression

Hi Shin,

My problem here is, I need total of Billable hour in one calculated field then I can perform the calculations.

The problem is it depends upon 2 filters . Order type-Billable and Source Object-604653AV

SUM(IF( [Order Type] = "Billable") THEN [Hours] END) ---- using this I do not get results.

If you could just help me with this, it would help.

Thanks !

Saket

• ###### 13. Re: LOD Expression

Again, it's not clear to me at all which data source's which field are you talking about.

And excel requires password and cannot open.

What is "Hours" / "SC available Hours" ????

Shin

• ###### 14. Re: LOD Expression

Hi Shin,

If you see Billable Hours sheet which you created in screenshot. Ex- (Week 28 - 672)CAT2 source

SC Available sheet - Production-Components source -----Ex - (Week 28 - 1114)

NonBillable sheet - CAT2 source -----Ex - (Week 28 - 94)

Sickness sheet - Production-Components source ---- Ex- (Week 28 - 32).

Please only consider sheets marked with RED.

Using this I want to create calculated field -

HOurs not booked - SC Available - (Billable + NonBillable + Sickness )

Thanks !

Saket

1 2 Previous Next