You can't do table calcs (such as LOOKUP) in LODs.
So take the LOD syntax out of there and just do the logic after the colon. Also you will have to wrap [event] in ATTR() in that calc.
That will clear up the errors.
Whether or not this will give you the results you want will depend on the table calc settings you put on that calc. If you have the sheet displayed like your example, then TABLE(DOWN) should work.
Actually, if you need it to restart for every user ID, you may need to do a different table calc setting.
Can you upload a sample workbook? The settings will depend on what your data looks like and what you have on the sheet.
Joe, thank you on this one!
Sadly i can't upload workbook since it is clients data - i wish i could.
I can't help without a workbook.
You don't need a lot of data. Just two or three IDs and enough rows within an ID to exercise what you need to have done. In fact, you can probably toss out all the other columns that aren't needed for the viz you are trying to do. You don't need all the other sheets and dashboards in the workbook. Just the sheet(s) you need for this question.
Joe thanks, tutorial was really helpful!
I ve attached my workbook
I actually don't need any work sheets(i think) in my workbook since i just need to create couple of variables.
I created variable i needed w/o LOD but it still gives me no correct answer.
Event_1.twbx 6.5 KB
See attached. (I saved it as 2018.1 because my Beta 2018.3 didn't tell me what version your workbook came from. If you're a version lower than 2018.1, let me know.)
On Sheet 2 I took the sequence calc off filters. I made a [Previous Event] calc and put it on the sheet. Everything comes up NULL. That's because tableau's default for the table calc is TABLE(ACROSS). So basically it's taking every row (for instance 5.296/"about_plans"/first time row) and looking backward within that row for the previous value. And since there is only one value in that row, there is no previous, so the result is null. To see that table calc setting, right click on the Previous Event] pill on the ROWS shelf and select "edit table calc".
Now go to Sheet 2(2). Here I edited the table calc for previous event. Right click and select edit table calc. See what I have there. I'm telling it to process through all the dimensions on the sheet, in the order I specified (you can drag the dimensions up and down in the "Select Dimensions" panel). And I also told it to restart for every ID. This is important. I think you do NOT want the first Event of 7,758,333 to look at the last event of 5.296.
So you will see that the first [Previous Event] for every ID is always null. You want that.
There is another setting in there. Look at "At the level". Initially I had it set to DEEPEST. But when it's set that way, and the calc is on the second TIME value for "Attempt" under 5.296, it would see the first value of "attempt" and grab that value on the second row. So I told it to act at the level of [Event]. Now only the first row of a multi-row event will look backward. The second one sets itself to NULL. (I can make that set both rows of "attempt" to "about_plans" if needed.)
So now you have a calc that looks backward.
Next look at [Was my previous....]. Here I compare my current value to the previous event, and set 1 or 0 accordingly. Note that I have to set the table calc settings for this one too. Use the same as what we did for [Previous Event]. I compared "fail" and "start". You can modify and rename this calc to do specifically what you actually need here.
And if you drag that calc from the text shelf to filters, you can select for 1 and get only the rows you want to see.
Event_1_v2018.1.twbx 66.1 KB
Joe thank you!!
that was really helpful - i've implemented it and it is working!
I would really appriciate if you could help me with second thing.
So what i need to do is to understand whenever combination of specific events ever happened for user. For example, i've updated workbook and i need to know if "help" event is really helpful - does it lead user to success. I've tried to do that in similar manner to previously discussed task - e.g. build a sequence : if attempt event is followed by help and help is followed by success then 1, else 0. However, i quickly realised that it is not working because there are some events in between(in updated sample i've called them "something_else").
So i want to build a calculation that returns me user ID if user ever had combination of specific events and returns NULL if user did not. I prefer function to return user id instead 1 or TRUE because i think i will do distinctive count in of this column later on for my visualisation.
I think i know how to do something like that in python but really struggling to do so in tableau.
Event_3_v2018.1.twbx 82.7 KB
Take a look at the attached.
Here I added up all the 1s an 0s for each Chat ID. If greater than 0, then we met the condition somewhere. Grab that chat ID.
Notice that you get the value on each row. It's a table calc, so it processes all the rows in the table. Edit the table calc. It looks like this:
We have to set calc settings for all the table calcs involved in this equation. There is a pulldown (circled) that lets you make sure all the calcs are processing in the order you need. In the screen shot the original calc is set to what we had before. Pull down the list and edit the new calc. Notice that I didn't mess with "at the level" this time. Settings for component calcs do not have to be identical when you have nested calcs. I have some in my workbooks that have one calc with a complicated setting, another adding all those up across a row (TABLE(across),) and a third working within "cell". There is no limit that I've encountered to nesting and setting different ordering. It can get complicated, but you can do powerful things with this.
Anyway, now you can display the Chat IDs, filter with this calc, or whatever else you want.
Question for you: ID 5.296 has a "fail" and a "start" with an intervening Event. Would your business ever need to identify that? It can be done, but if you don't need that, I'll drop the question. (Your events are coming out alphabetically here. Maybe this is just bogus data and I'm seeing something that really wouldn't happen in your real data.)
Event_3_v2018.1 A.twbx 80.0 KB