1 2 Previous Next 15 Replies Latest reply on Feb 26, 2017 5:43 PM by Chris Wirth

# How to find first (sold) and then next?

In my case (simplified as an example), I basically have orders (the blurred column of customers) that have been placed in advance over 4 quarters of a year for different product categories (grades and standards).

I am able to create the below view that's showing a count of product and sub-product (grade and standard) x Qtr (A1-A4).

What I want to be able to show is for each product and customer:

1. Which quarter is the first time that the product is sold?

2. What is the next quarter that the product is sold?

Basically two more columns that would show the "earliest" Qtr there is an "item count > 0", and then another column that shows the next Qtr that there is an "item count > 0".

One of the challenges I am dealing with is that I am not using dates, but rather Quarters are identified as Q1, Q2, Q3, and Q4.It seems to me that this should be do-able using sets to define the Quarters and then a calculated field, but I am not sure how to go about this.

Many thanks for any help!

-Chris

• ###### 1. Re: How to find first (sold) and then next?

Hi Chris,

Now, I don't know exactly what your viz looks like or the structure of your data, but my first thought was a table calculation. Basically you create an INDEX() calculation, put it on the viz, set it to compute using Product and Quarter, restarting every Product. Then you'd put the Index() pill on the filter shelf and filter to show only the first two. See attached workbook for an example.

If this suggestion doesn't help you achieve your desired viz, could you share a screenshot of your current viz, or a sample workbook marked up with obfuscated data or superstore data?

Christina

• ###### 2. Re: How to find first (sold) and then next?

Thanks, Christina.

I opened the workbook, was there supposed to be an index calculation in there?

-Chris

• ###### 3. Re: How to find first (sold) and then next?

Christina,

I can only seem to get the first two quarters to be A1 and A2... even if the first time the product is sold is for A3 or A4.

I am likely doing something wrong because a table calculation should work I think.

-Chris

• ###### 4. Re: How to find first (sold) and then next?

You might want to do the index calc off of maybe a concatenated string that shows Quarter AND year? Like, if something is sold in 2017 Q3, then 2017 Q4, then 2018 Q1, then 2018 Q2, and you're only looking at the part that says "Q2," it's going to sort in alphanumerical order, so you'll want to make sure that you've got it set up so that your first sale is at the top of the list.

• ###### 5. Re: How to find first (sold) and then next?

Nah, I may have been imprecise in my language.

I don't see how an INDEX table calculation will give me the first and next occurrence of a future scheduled sale.

First occurrence of a sale:

A sale (of a grade and standard) may occur for the first time in A1, A2, A3, or A4.

A sale may occur for the first time in A2

A sale may occur for the first time in A3

A sale may occur for the first (and only time) in A4.

Next occurrence of a sale:

A sale may occur for the next time in A2.

A sale may occur for the next time in A3.

A sale may occur for the next time in A4.

I simply want the table calculation to tell me:

1. Which is the first Qtr (A1-A4) a product sells, and then,

2. Which is the next Qtr (A2-A4) a product sells

• ###### 6. Re: How to find first (sold) and then next?

I think we might be talking past each other a bit. This would go a lot faster if you shared a screenshot of your viz or a mock up of your data...

Does your data set only have records for future sales, or does it also include records of past sales, too? If it includes both, how do *you* know the difference between past and future sales?

• ###### 7. Re: How to find first (sold) and then next?

There's a screenshot of my viz in my first post.

• ###### 8. Re: How to find first (sold) and then next?

Oops, must have missed it! Where, precisely, do you want it to say what the first two quarters sold are? Can you mock up what you want the end result to look like? Perhaps in powerpoint or as a table in Excel?

• ###### 9. Re: How to find first (sold) and then next?

Ideally I just want 2 new columns added to that viz:

1. Earliest Interim (interim is what we call a Qtr.): Would be a value between A1-A4

2. Next Interim: Would be a value between A2-A4.

But I am flexible on any way I can present that data.

Thanks!

• ###### 10. Re: How to find first (sold) and then next?

OK, I think I've got it.

The table calc I was thinking of originally won't work, because you've got Quarters Sold on the columns there, so the index accounts for quarters even when no purchase is happening.

To get the "First Quarter" Date, I recommend an LOD, like {FIXED Product : MIN(Quarter Sold) }

Getting the "Second Quarter" date is a little trickier.

1. Create a Running Sum calc: RUNNING_SUM(SUM(Number of Records))

2. Set this to Compute Along Product, Quarter Sold; restarting every Product.

3. Create a second calculation, "Second Quarter": WINDOW_MAX(IF [Running Sum] = 2 THEN ATTR([Quarter Sold]) END)

Of course, you could always do a similar calculation to the one in step 3 for the "First Interim" value, but LODs are usually easier to work with than table calcs, and more flexible.

1 of 1 people found this helpful
• ###### 11. Re: How to find first (sold) and then next?

Thanks so much, Christina!

I am a little hamstrung because we use Tableau Server v.9; though I do have V.10 desktop which supports LODs (I don't think pre-V.10 does).

I will try both approaches...  and reply with any (inevitable ;-) questions.

Thanks! again!

-Chris

• ###### 12. Re: How to find first (sold) and then next?

Christina,

What would the table calc look like to get the first instance if I was stuck not being able to use LOD because I had to author in 9.3?

Thanks!

-Chris

• ###### 13. Re: How to find first (sold) and then next?

I'm pretty sure LODs were introduced in v9, but the table calc would look the same, just set it to Running Sum=1 instead of 2.

• ###### 14. Re: How to find first (sold) and then next?

Christina, you are awesome!!! I used the table calc approach and it works perfectly.

Can I use LOD syntax right in the calc field editor? I will try that next.

Thank you SOOO much!

-Chris

1 2 Previous Next