It's not a dumb question at all. It is a little bit difficult to answer without a some more context. One thing that often helps is to post a packaged workbook
Screen shots are also nice (especially in this case, since you are dealing with RAWSQL). Also, it would be helpful to know how close you are, what errors you are getting, or where exactly you are getting stuck.
Welcome to the forums!
As Joshua says it's hard to answer your question without more context but I'll make a suggestion that might help you anyway. Couldn't you create a calculated field to find the earliest install date? Something along the lines of MIN(IF event = 'install' THEN DateField END).
Maybe you need to put the UserId on the Level Of Detail shelf, maybe it's already accounted for in the filter, it's hard to say without seeing a packaged workbook.
This could be an easier solution than rawsql if it works in this case.
Many thanks for your quick response.
Sorry, you're quite correct - would be much easier if I had uploaded an example. As you suggested, however, some of the data is sensitive so I had to scrub it.
Attached is a much simplified version of the data I'm working on - there are multiple UIDs with a 'Load' event and an 'Install' event. These events are seperate database entries. I would like to run a calculation that will lookup the 'Install' event and return the date field. I would then like to compute the number of days between the 'Load' event and the relevant 'Install' event. This needs to then be mapped out on the x axis by date of 'Load'
I started off by mapping out the Date field on the x axis and limiting the viz by restricting the event type to 'Load', however that seems to then restrict the results of the calculation you suggested.
I've attached an Excel version of what I am trying to achieve (I'm much more at home with Excel!) - note I did the pivot table in Open Office on the Windows partition of my Mac - if it behaves strangely I blame that!
Many thanks again - it's a fairly new job, and software package to me, so I want to try and impress!
Sorry for the slow reply - I've been working on this today and I think I'm so close I can almost taste it!
I'm passing a RAWSQL_DATE function back to the database using the following formula - this should return the minimum date for each UID where the event type is 'Install'
WHERE %2 = 'Install'
GROUP BY %3, %2
ORDER BY %3, %2
GROUP BY %1
ORDER BY %1", [Date],[Event],[UID])
I...... think that should do it. But I'm getting the following error:
Microsoft JET database error 0x80040E10: No value given for one or more required parameters.
I can't quite see where the missing parameter might be. Any ideas?!
Thanks in advance
Hey to anybody following this, I think I cracked it.
I was probably over complicating the situation by following the RAWSQL route (from what I've been able to ascertain, the RAWSQL calculations are only really useful for obtaining aggregate or singular data from the underlying table, rather than doing any targeted data extrapolation.)
I simply edited the connection, bringing in a carbon copy of the base table, then altered the SQL on the query:
SELECT `gamelogs`.`entrydate` AS `entrydate`,
`gamelogs`.`event` AS `event`,
`gamelogs`.`uid` AS `uid`
,`gamelogs1`.`entrydate` AS `InstallDate`,
`gamelogs1`.`uid` AS `Install_UID`,
`gamelogs1`.`event` AS `InstallEvent`
FROM `default`.`gamelogs` `gamelogs`
LEFT OUTER JOIN `default`.`gamelogs` `gamelogs1` ON (`gamelogs`.`uid` = `gamelogs1`.`uid`)
WHERE `gamelogs1`.`event` = 'install'
This results in my data extract having a new column with the minimum event date for each UID at the Install Event.
I then create a nice simple datedif('day',[entrydate],[InstallDate]) as my calculated field for 'DaysSinceInstall' - make this field a Dimension, stick it on the Rows shelf, Date on the Columns shelf, Number of Records on the Marks and I'm done!
I'm not sure this is the most sensible / scalable solution, but it works for me! This has been a good introduction to Tableau - Great to see that the system has this amount of flexibility to obtain workable solutions.
I'll sleep tonight!
Apologies for not getting back to you earlier, busy weekend for me, but well done on figuring it out for yourself. I'm sure it's also possible to solve this using a Tableau only solution, although I'm from a database background so always deal with these situations altering the data connection using custom sql.
If you're comfortable writing SQL once you get to grips with the nuances of tableau you'll find this a great piece of software. Tableau uses MS Jet SQL which could cause you some headaches in future if you're used to SQL Server, especially if using date functions...but I'm sure you'll overcome these issues once you run into them :-)