I've built a calculated field called "Days Between 1st and 2nd Purch (hopefully!) using the following equation, as inspired from one of the threads I listed in my original comment above:
MIN([Order Date]) - LOOKUP(MIN([Order Date]),-1)
I'm trying to use this equation in the following way, but I keep receiving "Out of Memory" errors on my 32-bit machine:
1) I've placed this calculated field on the Text shelf of my sheet entitled "Order Dates by Customer Number" in the workbook I attached to the comment above.
2) I've clicked Edit Table Calculation, clicked Compute Using, and selected Advanced. That takes me to the screen below
The partitioning shown above makes sense because I do want my equation to be applied to each Order in a Customer ID, but not applied to the last Order Date in one Customer ID and then the first Order Date in the next Customer ID. So click OK on this screen and then run the calculation. The calculation runs for about six seconds and then tells me I'm out of memory, even when Tableau is the only thing open.
To anyone reading this thread, how does this approach seem? How else can I answer the three questions I posed in my original post?
You will need a bunch of calculated fields to do this.
Check in the attached.
Check the Order Dates by Customer Number to understand the logic behind the calc. Pay attention to the Compute using - Advanced section or each calculated field.
On the Final View , it`s only a duplicate of the same sheet but the unwanted columns are hidden (unselect Show Header), and a filter is used to keep only the first row of each customer id.
Thanks for sending this along. I haven't yet looked at it, but I wanted to say thanks before too much more time passed and you thought I was rude. I'll take a look at this tomorrow morning and test it in my own copy of the SuperStore sample data set and in the real dataset I'm actually working with.
I looked over your solution and it makes complete sense to me. Thank you for sending me your workbook.
I have tried to recreate your solution in my copy of the SuperStore dataset, though, and that's where I have an issue. My computer crashes out with an "out of memory" error when I use Edit Calculations, click Advanced, and then set the partitioning for the First Order or the Second Order calculations.
Is there a less memory-intensive method to identify each Customer ID's first and second order?
Can`t help much with that. How much ram do you have on this computer, how much is not in use ?
Superstore is a very small dataset, can you open it in Excel ?
1 of 1 people found this helpful
I'm on my phone and can't look at this in detail, but I do know that you can get out of memory errors when the Compute Using of a table calc is on a date or datetime dimension and Tableau triggers domain completion and tries to create a value for every possible combination of date and the other dimensions in the view.
To avoid unwanted domain completion, make all your date dimension pills on Rows, Columns, & Pages discrete measures using ATTR/MIN/MAX/etc. (you might need some calculated fields to do this), and put the needed date dimensions on the Level of Detail Shelf.
I just tried rerunning your solution in a small subset of SuperStore (I deleted about 90% of the rows in the original SuperStore extract) and now the solution is returning rows for dates that don't have any order attached to them at all. For example, there isn't an order ID for Order Date 1/22/2010 in the screenshot below, but you can see that a row appears for Order Date 1/22/2010. Could you please let me know what I have done wrong? I don't think I did anything different than you did, but clearly I am getting a different result set. I have also attached a workbook containing my implementation of your solution up to calculating the First and Second Order Dates.
I already have my date fields as discrete, but it sounds like I should also use the ATTR function on these already discrete fields, and then place these ATTR-modified fields on the shelves? Did I correctly understand your advice?
From the screenshot you'd posted where you were running out of memory, you had the Compute Using on Order Date, partitioning on Customer ID. Tableau's default densification behavior in this case is to "complete the domain" of Order Date for each Customer ID, so in my copy of Superstore Sales that means that Tableau will be creating 4.8 million entries (1427 Order Dates for 3403 Customers). One key (among several) to triggering densification is the presence of one or more dimensions on Rows, Columns, and Pages, so by removing the triggering dimension(s) from those Shelve(s) we can stop the densification. However, those dimension(s) may still need to be "in the view" for calculations to work and we may still want something like them on Rows, Columns, and/or Pages to have the display be what we want. So we can put the dimension onto the Level of Detail Shelf, and use an aggregate equivalent such as ATTR() for the dimension to get the layout.
The workaround I described therefore looks like this:
A table calc with a Compute Using on Order Date only will not trigger domain completion with this arrangement.
Does that make sense?