Search code examples
tableau-desktop

Tableau LOD calculation

enter image description here

I am a newbie in Tableau and have no ideas how to solve the following task:

I have a table. Each row contains fields with date (date), customer id (string), order id (string), price (numeric), quantity (numeric). But not all data (rows) contains the correct information. One order can contain (but not must!) several rows with different date. In that case, the correct rows are with the last date, the rest are just garbage. So, I need to get the correct quantities of orders and customers per day.

The algorithm is simple: to get last date of each order, to filter rows of each order with only this date – these are correct data (the rest are wrong). After that to calculate number of unique orders id and customers id per day.

In conclusion, to plot a diagram with quantity of orders per day with tooltips of customers’ quantity.

I can do it with Python (just several strings needed) and I know it can be done via LOD, but I can’t. It is like an absolute obstacle now for me(((

Maybe, Python's code clarify the approach:

for order in data['order_id'].unique() : 

correct_date = data.query('order_id == @order')['date'].max()

data = data.drop(index=data.query('order_id == @order and date != @correct_date').index)
Solution

  • If I correctly understand your problem you can use LODs calculated fields to achieve your target. I purposely used two fields instead of one for ease of reading

    Field: Last update of order

    [date] = { FIXED [order id] : MAX([date])}

    This field should be True only in the last row of each order. With this, you can extract only values on the last date.

    Field: Last quantity per order

    { FIXED [order_id] : MIN(IF [date] = [Last update of order] THEN [quantity] END) }