10 Replies Latest reply on Jan 24, 2017 6:58 PM by Vishal D

# Want to calculate max(time_taken) for each unique field value

Want to calculate Gallons/ per minute.

Total Gallons = Sum of all (Net Qty) by each Lane e.g 12494

Total Minutes = Sum of max(time_taken) for each unique ticket number  e.g 1918

Gallons per minute = Total Gallons/ Total Minutes e.g 6.51

How do I calculate 'Total Minutes' logic?

See attached spreadsheet for sample data.

• ###### 1. Re: Want to calculate max(time_taken) for each unique field value

Vishal,

Looks like an application for LOD expressions.

[Gallons per Minute by ticket]

`{ FIXED [TKT_NBR] : SUM(NET_QTY) } /{ FIXED [TKT_NBR] : MAX(DATEDIFF('minute', [BEGINNING_DT_TM], [END_DT_TM])) }`

Disclaimer: Since you did not attached a packaged Tableau workbook showing your progress so far, the above formula has not been tested in any way.

• ###### 2. Re: Want to calculate max(time_taken) for each unique field value

The formula seems to look right but there is something with my data. I am getting weird results.

Below is data snapshot for ticket number 615780  in excel snapshot and what I want is Max Load Time = { INCLUDE [TKT_NBR] : MAX(LoadDuration) } which should ideally be 18.88 but I am getting different results in tableau

issue is the load duration is getting rolled up or sometimes doubling up in Tableau and due to which Max load time is not getting calculated correctly.

Once I get Max load time then I can calculate Gallons Per Minute.

Do I need to add another field in INCLUDE which will make each record unique?

Attached twbx

• ###### 3. Re: Want to calculate max(time_taken) for each unique field value

Vishal,

I have created new worksheet with excel data.

[Max Ticked Number]

{fixed [Tkt Nbr]:max([Time taken])}

[Net Q / Max Tick]

SUM([Net Qty])/max([Max Ticked Number])

And

Try to make all the filters to "context"

Filters and Level of Detail Expressions

Thanks,

Shin

1 of 1 people found this helpful
• ###### 4. Re: Want to calculate max(time_taken) for each unique field value

I think right now my issue is the data, the sample data I posted looks ok and hence the formula works but my real data is little different.

I am doing

[Max Ticked Number]

{fixed [Tkt Nbr]:max([Time taken])}

But same [Tkt Nbr] is part of different bay's (which is weird and we are trying to see if data is bad) due to which my numbers are acting weird.

Question - If I have below snapshot how does the logic work?

Will the [Max Ticked Number] logic work taking into consideration all below context filters?

e.g for Facility Name = Billings & Transaction Code = STE & Transaction Date = This Year give me [Max Ticked Number]?

If same [Tkt Nbr] could be part of different 'Bay' then should I bring 'Bay' in filters and make it context as well?

• ###### 5. Re: Want to calculate max(time_taken) for each unique field value

If you look at below data(data record highlighted in red) all dimensions are same and only measures make the data record unique

Tableau summaries the data default and is adding the measures. How do I force tableau not to summarize the measures so that it calculates the formula correctly??

[Max Ticked Number]

{fixed [Tkt Nbr]:max([Time taken])}

• ###### 6. Re: Want to calculate max(time_taken) for each unique field value

Vishal,

I'm confused.  Excel header and Tableau field name is way different.

Could you specify the field of Excel column "A" ~ "L" in Tableau sample.

Thanks,

Shin

• ###### 7. Re: Want to calculate max(time_taken) for each unique field value

Excel column "A" FacilityCd = 'Facility Name' in Tableau sample

Excel column "L" 'MeterNbr' = 'Meter Number' in Tableau sample

• ###### 8. Re: Want to calculate max(time_taken) for each unique field value

Excel column "A" FacilityCd = 'Facility Code' in Tableau sample

Excel column "B" FacilityName = 'Facility Name' in Tableau sample

Excel column "C" Lane = 'Bay' in Tableau sample

Excel column "D" TktNbr = 'Ticket Number' in Tableau sample

Excel column "E" BeginningDtTm = 'Ticket Beginning Date Time' in Tableau sample   - LoadDurationMinutes is calcuated in backend using BeginningDtTm and EndDtTm wo we can ignore these fields if not needed

Excel column "F" EndDtTm= 'Ticket End Date and Time' in Tableau sample

Excel column "G" NetQty = 'Facility Name' in Tableau sample

Excel column "H" GrossQty = 'Facility Name' in Tableau sample

Excel column "I" LoadDurationMinutes= 'Facility Name' in Tableau sample

Excel column "J" SalableProductCd= 'Facility Name' in Tableau sample

Excel column "K" InventoryProductCd= 'Facility Name' in Tableau sample

Excel column "L" 'MeterNbr' = 'Meter Number' in Tableau sample

The way tableau is aggregating measures is causing the below formula to not work.

[Max Ticked Number]

{fixed [Tkt Nbr]:max([Time taken])}

• ###### 9. Re: Want to calculate max(time_taken) for each unique field value

There are so many dimensions and measures.

Then What is ([Time taken]) in Tableau ?

Thanks,

Shin

• ###### 10. Re: Want to calculate max(time_taken) for each unique field value

I think I figured it out, it needed two levels of INCLUDE as the data was unique at 2nd level.

{INCLUDE [Ticket Number]: MAX(  {INCLUDE [Meter Number]: MAX([Load Duration Minutes])}  ) }

thanks all