Hi Kant

Can you explain more. "pick the first 2 words in the descending order of their frequency" means what you going to pick.

Attach workbook for further.

Thanks

sankar

hi,

i believe he is supposed to pick 2 words from a month which have maximum occurences in that month.

thanks,

sarathi

Hi Kant, are you still looking for help with this?

Hi Kant,

You can use INDEX() table calculation to rank each search term within each Month-Year and then filter on your INDEX ranking calculation. Please see the detailed instructions below and the attached workbook for an example.

1. Create a new calculated field Rank Search Terms and enter the formula INDEX()

2. Edit the following Default Table Calculation settings for the new Rank Search Terms field:

• Set Compute using to Advanced. In the Advanced screen move Year, Month, and Search Term into the Addressing area (in that order) and Sort on Frequency in Descending order.
• At the level: Search Term
• Restarting Every: Month

3. Drag the new field Rank Search Terms to the Rows shelf in between Month and Search Terms

4. Add Rank Search Terms onto the Filters shelf and make sure that all the same Default Table Calculations are applied. Set to filter when the value is At Most 2.

5. Right click Rank Search Terms in the Rows shelf and uncheck Show Header.

Sarah

Hi Sarah,

I'm trying to do something that I feel should be similar, but for the life of me I can't get it to work. (And I can swear I've done it a hundred times before.)

I've got a bar chart tracking server performance daily for a range of months. But I only want to show the last 2 months in my view. I'd have thought something like last() set to compute along table down should do it, but it didn't, and no combination I could tinker up in the advanced options gave me the result I'm after.

Please see attached for a recreation of what I want to achieve - now, I'd expect, with this layout and filter to only see December and November in my view. For some reason I get some of October as well. Can you help? I feel like Tableau is gaslighting me.

Thanks in advance,

Willem

Hi Willem,

Before we discuss what solution would work, it might be helpful to review why last() doesn't work in your setup. I added last() as a label to your original graph and removed the last() filter. Now looking at the graph, you can see that last is actually counting the occurrence of each day of the month (where the first occurrence is 0.) This is causing issues because not all months have the same amount of days. If all months had the same amount of days or if you removed day from the view the last formula would work.

On the second tab in the workbook I included a solution that works with your view. You can use a Top N filter to get the last # of months. I also added a parameter Top N Months to use to specify the number of months you want to view. If you add Year to the graph, you can go back further than the last 12 months.

• Add month(Date) to the filter shelf
• Filter by Top [Last N Months] by Order Date (Maximum)

Sarah

Excellent, thanks. That's so much simpler.

Hi Kant,

I'm just getting back to this thread. What Sarah wrote is essentially how I'd do it. There'd be some minor differences in how I'd set the calculations up but the results would be the same.

Jonathan

Hi Jonathan,

I'm curious how you would set up the calculations and what are there benefits of setting it up that way vs the way I suggested?

Thanks,

Sarah

Hi Sarah,

Like I said, the differences were minor:

- On the Advanced Compute Using, the At the Level on Customer Name is not needed because Customer Name is already the deepest level of addressing dimensions, so the default Deepest can be used. The only time I turn on At the Level is when I explicitly want a table calculation to not increment addressing based on the lowest level addressing dimension(s). There's also a subtle behavior where At the Level can trigger densification behaviors when the dimension use for At the Level is moved to the Level of Detail Shelf, I like to avoid giving Tableau the opportunity to trigger densification when I don't want any!

- Use one of the RANK functions instead of INDEX. I prefer RANK calcs for the following reasons:

     1) They ignores Null values (which might be put in by densification).

     2) RANK, RANK_MODIFIED, and RANK_DENSE all support ties, which INDEX doesn't, though long ago James Baker created some calcs that use INDEX() and PREVIOUS_VALUE() to enable ranking with ties.

     3) The measure used to sort the partition is declared as the argument to the RANK calculation, whereas with INDEX() we have to do a pill sort or use an Advanced Compute Using with a pill sort. I find that makes the use of the rank calculation more discoverable. In this case a RANK_UNIQUE(SUM([Number of Records])) with a Compute Using on Customer Name provides the same results in fewer clicks to set up and the addressing of the calc is also discoverable with one click instead of the two clicks for INDEX+Advanced.

Thanks for asking, I like seeing how other people set things up and sharing how we approach Tableau!

Jonathan