12 Replies Latest reply on Mar 18, 2016 6:40 AM by Hemalatha B

# How do i use a if statement with aggregated column and dimension not in View

So i have data in below format. Median Sales Price is a calculated column which is a median value of sales price per part number.

I need to get for each part number how many quotes have a quote price greater than median sales price and then display the sum of quantity for the same.

 Part Number

 Quote_Id
 Quote_Price_per_item
 Median Sales Price

 Quantity
 PART001
 Q1001
6021.420
Q10022021.410
Q10039021.470
Q10044021.412
Q10051021.45
PART002Q200112060500
Q20025060380
Q200310060430

 Part Number Quote_Id Quote_Price_per_item Median Sales Price Quantity PART001 Q1001 60 40 20 Q1002 38 40 10 Q1003 90 40 70 Q1004 40 40 12 Q1005 50 40 15 Q1006 60 40 20 Q1007 70 40 40 PART002 Q2001 120 105 500 Q2002 90 105 300 Q2003 150 105 800 Q2004 110 105 450 Q2005 120 105 500 Q2006 130 105 650

Desired Output:

Part NumberNo of Quotes
Total Quantity
PART0013112
PART0022930

Kindly help in understanding which calculations would help achieve the desired output.

• ###### 1. Re: How do i use a if statement with aggregated column and dimension not in View

Are you looking for something like this?

I just created 2 calculated fields with following formulas.

IF [Quote Price per item] > [Median Sales Price] THEN [Overall Quantity] ELSE 0 END

IF [Quote Price per item] > [Median Sales Price] THEN 1 ELSE 0 END

Or I am missing something here.

• ###### 2. Re: How do i use a if statement with aggregated column and dimension not in View

Thanks Satish. Quote id will not be displayed in the view and median sales price is something i have created as calculated field using sales price column from source. If i use your calculation then i will get an error as cannot mix aggregate and non aggregate arguments. If i then sum quote price and try to compare with median sales price then it is summing up data for all quote ids for that part and then comparing with median sales price which is wrong because my intent is to check each row and wherever condition is satisfied only then sum. Hope its clear.

• ###### 3. Re: How do i use a if statement with aggregated column and dimension not in View

Satish in your workbook u are assuming that median sales price is coming from source. Its not. Only sales price is coming from source. I have created Median(Sales Price) as a calculated column. Yes its easier if it was coming from source and we can directly use it in If condition without any issues but that's not the case here.

• ###### 4. Re: How do i use a if statement with aggregated column and dimension not in View

can you tell me the formula you are using to calculate median price?

• ###### 5. Re: How do i use a if statement with aggregated column and dimension not in View

I think i figured it out myself while reading some LOD related blogs. Thanks anyways for your help Satish.

• ###### 6. Re: How do i use a if statement with aggregated column and dimension not in View

I have another issue now.The sales price is coming from a different source and quote price from another source. How do i now compare each quote's quote price with median sales price from second source and get only those quotes that have a quote price greater than median sales price?

i need to get an overall quantity of 162 for Part001 and 2400 for part002 but its taking the entire data and summarizing.

I have attached the workbook for reference.

• ###### 7. Re: How do i use a if statement with aggregated column and dimension not in View

Here is the workbook

• ###### 8. Re: How do i use a if statement with aggregated column and dimension not in View

did you find solution or still looking for it?

• ###### 9. Re: How do i use a if statement with aggregated column and dimension not in View

I have written to tableau support for help. I haven't been able to resolve the issue.

• ###### 10. Re: How do i use a if statement with aggregated column and dimension not in View

You can do something like this maybe:

All calculations should be set to compute using pane(down). Of course this would change if you set up the view differently based on pill placements. Look at calcs 1, 2 and 3 in the attached. You can keep quote id on rows and uncheck show header so that addressing is properly considered.

Attached 9.2 WB.

• ###### 11. Re: How do i use a if statement with aggregated column and dimension not in View

Thanks Pooja. That's exactly what i wanted. i did play with the table calculation today and kind of arrived at what u have provided. Anyways, thanks so much for helping.