1 2 Previous Next 16 Replies Latest reply on Feb 7, 2019 1:10 AM by Shyam Sundar Ranganathan

# Fixed Column Referencing

Hello Team,

I have test sales information across location for a supermarket. The idea is that I should have the top 3 products (In terms of Total sales) for a chosen location and I want to compare the measures (Quantity,Average Price) for those products with other locations

Lets say I chose the location to be New York and the location I would like to compare with is California, so the result should have the  top 3 products of New York and the information for those products in California against it. Incase if California do not sell a product that's listed for New York then the value for those measures should be empty

.
I have attached a sample tableau workbook. Kindly have a look, looking forward to your solutions.

Thanks,

Shyam

• ###### 1. Re: Fixed Column Referencing

Shyam,

You can try this.

First you can get the Total Sales per Item per Store with

{FIXED [Store Location],[Items Sold]: SUM ([Total sales])}

Next, you will need two parameter (String - List - Add from field - Store Location),  one for the top 3 selection Location, and one for the other Location you want to compare with.

Next you can create  a boolean returning True on all product rows corresponding to the Top 1 product of the first parameter selection

{FIXED [Items Sold]: MAX(

[Compare top 3 from] = [Store Location] and [TotalSalesPerItem-Store]  = {FIXED [Store Location]: MAX([TotalSalesPerItem-Store])}

)}

and another one for top 2

{FIXED [Items Sold]: MAX(

[Compare top 3 from] = [Store Location] and [TotalSalesPerItem-Store]  = {FIXED [Store Location]: MAX(if not [isTop1] then [TotalSalesPerItem-Store] end)}

)}

and one for top 3

{FIXED [Items Sold]: MAX(

[Compare top 3 from] = [Store Location] and [TotalSalesPerItem-Store]  = {FIXED [Store Location]: MAX(if not [isTop1] and not [isTop2] then [TotalSalesPerItem-Store] end)}

)}

Next instead of using Items Sold in the view  you can compute another Dimension returning only the top 3

if ([isTop1] or [isTop2] or [isTop3]) then [Items Sold] end

Bring this on the Rows and exclude the nulls

and another dimension for the Store Location

if [Compare top 3 from] = [Store Location] or [with same products from] = [Store Location] then [Store Location] end

Bring on the Colums and exclude the nulls

See in the attached

Michel

1 of 1 people found this helpful
• ###### 2. Re: Fixed Column Referencing

Hello Michel,

Thank you for your solution ! Looks very close to what I am looking for. The solution provided takes top 3, but if I want to have lets say top 25 or 10 then is there a another way apart from creating 25 or 10 boolean calculation fields ?

Is there a way to always keep the 'compare top 3 from ' field to the left ? The column position moves when i change the reference country. The reason is because visually its more convenient to have the main column to the left and the reference columns to the right side of it.

Thanks,

Shyam

• ###### 3. Re: Fixed Column Referencing

Shyam,

Unfortunately, I don't see how to do this only using the Rank function.

Maybe someone have a better idea .

For the second question, you could do two sheets,  each one having the Store Location corresponding to the parameter, and bring both in a dashboard.

• ###### 4. Re: Fixed Column Referencing

Hi, Shyam

Below is the screenshot of steps and the result for reference.

When showing top 5

Hope this helps

ZZ

• ###### 5. Re: Fixed Column Referencing

Hello Zhouyi,

Thanks for the answer ! In your dimension, you have - Location , could you tell me what it is ? Is it possible to share the Tableau workbook

Thanks,

Shyam

• ###### 6. Re: Fixed Column Referencing

Hi, Shyam

the locaiton just a filter to filter out other locaitons

let me know if you have question.

ZZ

1 of 1 people found this helpful
• ###### 7. Re: Fixed Column Referencing

Nice job Zhouyi !

I should consider using sets more often...

Michel

• ###### 8. Re: Fixed Column Referencing

Thank you Zhouyi  and Michel for your valuable inputs , was really helpful. As Michel mentioned, sets should be used often

• ###### 9. Re: Fixed Column Referencing

Hello Zhouyi,

I tried your method and it worked in this dataset, but when I extended the same to the main dataset  It wasnt working. I am not sure what could be the issue and the file is too large to share it here.

When I create the set and move it to the filters there are no rows displayed.

Thanks

Shyam Sundar Ranganathan

• ###### 10. Re: Fixed Column Referencing

HI, Shyam

What's the difference between the two data sets? Is it the store location name as the filter should be exactly match with the name input?

ZZ

• ###### 11. Re: Fixed Column Referencing

Hello Zhouyi,

The variable names are different but I did map them all the same way as you have done in the sample workbook. I am not sure where exactly is the issue .

Thanks,

Shyam

• ###### 12. Re: Fixed Column Referencing

Hi, Shyam

It is not easy to come up with an idea of where the problem is without any clue

ZZ

• ###### 13. Re: Fixed Column Referencing

Hello Zhouyi,

I agree , but I solved the issue few mins before . The issue was on filtering and for my dataset I had to use Context Filters.

But the entire logic was based on your approach. Thanks again for your response.

Shyam

• ###### 14. Re: Fixed Column Referencing

Hello Zhouyi,

In addition to the calculation made in the workbook, I have a another question.

Right now we are comparing 2 locations .Home location and another location. Is it possible to compare home location  and Entire US together.

So I can compare how the home store has performed against the entrie region

Thanks,

Shyam

1 2 Previous Next