
1. Re: Want to calculate max(time_taken) for each unique field value
Jamieson Christian Jan 23, 2017 9:01 AM (in response to Vishal D)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
Vishal D Jan 23, 2017 7:50 PM (in response to Jamieson Christian)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

Gallons Per Minute.twbx 230.5 KB


3. Re: Want to calculate max(time_taken) for each unique field value
Shinichiro Murakami Jan 23, 2017 10:11 PM (in response to Vishal D)Vishal,
I have created new worksheet with excel data.
Please refer the formula.
[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

Gallons Per Minute_SM_10.0.twbx 261.6 KB


4. Re: Want to calculate max(time_taken) for each unique field value
Vishal D Jan 24, 2017 7:31 AM (in response to Shinichiro Murakami)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
Vishal D Jan 24, 2017 7:54 AM (in response to Vishal D)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
Shinichiro Murakami Jan 24, 2017 11:39 AM (in response to Vishal D)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
Vishal D Jan 24, 2017 11:50 AM (in response to Shinichiro Murakami)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
Vishal D Jan 24, 2017 12:09 PM (in response to Vishal D)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
Shinichiro Murakami Jan 24, 2017 3:16 PM (in response to Vishal D)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
Vishal D Jan 24, 2017 6:58 PM (in response to Shinichiro Murakami)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