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.
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.
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
Jan 23, 2017 10:11 PM
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
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?
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])}
Jan 24, 2017 11:39 AM
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
Excel column "A" FacilityCd = 'Facility Name' in Tableau sample
Excel column "L" 'MeterNbr' = 'Meter Number' in Tableau sample
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])}
Jan 24, 2017 3:16 PM
There are so many dimensions and measures.
Then What is ([Time taken]) in Tableau ?
Thanks,
Shin
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