I do not understand your question. You have a [Week_End_Date] field and a [Date] field in your data. The ID field looks like a text string that has a date encoded in the first 8 characters.
You can use DATEDIFF('day',[StartDate],[EndDate]) to calculate the difference in time (in days) between two date-formatted fields.
If you want to convert your ID field to a date for use as either the [StartDate] or [EndDate], you could do so with:
MID (STR([ID]/1000000000),5,2)// month#
MID (STR([ID]/1000000000),7,2) // day#
LEFT(STR([ID]/1000000000),4) // year#
Does that help?
Edit: I assume you are referencing the answer to your question here: http://community.tableau.com/thread/122301
We may need to use a lookup, but Gallop has not provided enough information yet.
Your calculation as it stands does not work, and we would need to understand the partitioning.
Every ID in that data file only has one product and either I or UN associated with it, but never both.
I would expect that we are missing data here
From your explanation, I would expect to see:
An ID with a product name (maybe more), and for each ID/product combination, an I (installed) row and a UN (uninstalled) row
I'm guessing the week_end_date is irrelevant, as is the fact the ID looks like a date string? Thanks for mocking up the data - I think we just need a better set of mocked up data.
Here's an example with a better data set. I had to include a dummy row because JET was changing the ID to a number with an exponential. I forced it to a text column.
It uses: datediff('day',lookup(attr([date]),-1),attr([date]))
And the partitioning uses date to order the lookup, with product and ID on the left side of the advanced calculation block (as we want each unique install)
diff.twbx.zip 36.5 KB
Your sample data does not contain double entries (one for install day and the other for uninstalled date). For each ID, you should have the start and end date per ID in each row.