Tableau calculated field dates

0
votes

I was trying to do a calculated field and I cannot get it to work right.

First I tried to duplicate what I did in access... 

    =DateDiff("d",[90 Days to Exp],[Expiration])

but that gives me an error

I did some research and found something that gave me valid calculations... 

    window_sum(datediff('day',
                lookup(min([90 Days to Exp]), 0),
                min([Expiration])))

that only shows 90 days which I already knew

I want to make sure it is doing a count down of days to "Expiration" so I changed it to...

    window_sum(datediff('day',
                lookup(min(NOW()), 0),
                min([Expiration])))

also tried....

    window_count(datediff('day',
                lookup(min(NOW()), 0),
                min([Expiration])))

still a valid calc but the numbers are way off.... I tried min and max with and without... and I still can't seem to get an accurate count down

1
tableau-apiwindow-functionscalculated-field

1 Answers

1
votes

Step back. Instead of giving us random pieces of code, explain what you're trying to achieve (what you expect as a result) and what your db looks like (for instance, what are the fields).

I'll give a solution to what I think you're trying to achieve on what I think you have as database.

I believe you have a database that each entry is a product that have an expiration date ([Expiration]). What I think you're trying to achieve is to know how many days are left until expiration date.

So, if you want to calculate this for each entry in your database, the formula should be:

DATEDIFF('day',NOW(),[Expiration])

It's very straight forward, you're calculating how many days there are between today and expiration date

I have no idea why you're using table calculations in your suggestions, but if this calculations should not be done for each entry (but rather for a collection of entries), then table calculations can be useful, but it's impossible to suggest anything if I don't understand precisely what you want