TABLEAU CALCULATED FIELD OPTIMIZATION

Question

I have a calculated field called FirstSale where I observe the first instance of a product that sells more than 80% of its inventory.

I look at the product ID and the timestamp (converted to string) and the % of inventory sold.

How can this query be optimized to not depend on this many fields OR how can this be converted to a SQL query.

Calculated Field Logic:

    IF STR[SaleDate]) = 
    {FIXED [ID], {FIXED [ID], STR([SaleDate]), [Inventory %]:
    IF MIN([Inventory %]) > 0.8
    THEN 1 ELSE 0 END}: MIN([STR(SaleDate]))}
    THEN 1 
    END

The data looks like this

Where there is a product ID, Sale Date, Inventory % and the last column (with 1s and 0s) is the calculated field.

Essentially, The goal is that the calculation should return 1 only for the first time a an ID shows Inventory % > 80%. In all other cases, return 0.

For example looking at the second ID, the only value combination that should have a 1 is October 28 (2020083008056, October 28 2020, 84.00%, 1 ) and all other values should return 0.

So the full return for the second ID would be

(2020083008056, October 28 2020, 84.00%, 1 )
(2020083008056, October 29 2020, 84.36%, 0 )
(2020083008056, October 30 2020, 84.67%, 0 )
(2020083008056, October 31 2020, 84.67%, 0 )

To be honest, it's hard to understand what's happening here. But I guess it's slow; FIXED can be slow. Is it necessary to convert the dates to string? Keeping as date should be faster. What does this do? {FIXED [ID], STR([SaleDate]), [Inventory %]:IF MIN([Inventory %]) > 0.8 THEN 1 ELSE 0 END} — Andy TAR Sols
If it's possible to lose the FIXED, then lose it. Without seeing the data it's hard to make a solid recommendation. — Andy TAR Sols
{FIXED [ID], STR([SaleDate]), [Inventory %]:IF MIN([Inventory %]) > 0.8 THEN 1 ELSE 0 END} This is the piece that checks if the inventory amount % is greater than 0.8. The FIXED is meant to make the query focus on the product ID, SaleDate and Inventory %. So basically per product it is checking to see at what point did the Inventory % exceed 80%. Even if I can convert this to SQL, that would be helpful because this calculation is rather expensive. — swagless_monk
Why do you keep converting dates to strings? That is slow and, it appears unnecessary. And as Andy says, only use LOD calcs when you need to. People often reach for them unnecessarily — Alex Blakemore
I agree and have removed. I have update to show how the data looks and the expected result — swagless_monk

AnilGoyal AnilGoyal · Accepted Answer · 2020-11-07T15:33:15

I have recreated some sample data to solve your problem. If I have not misunderstood your LOD calculation should be pretty much simpler. Let's have a look.

Sample data re-created

Added one CF just to check whether inventory sale is greater or equal to .80 and added it to view to create a data like you have shown.

Now add your desired field with calculation as

{FIXED [Prod id] : MIN(
IF [Greater than 80]=1 then [Date] end)} = [Date]

Adding this field to view/filter should serve your purpose. See it

Still if you want returns as 0 or 1 use this calculation instead

IF {FIXED [Prod id] : MIN(
IF [Greater than 80]=1 then [Date] end)} = [Date] 
then 1 else 0 end

TABLEAU CALCULATED FIELD OPTIMIZATION

1 Answers