1 2 Previous Next 15 Replies Latest reply on Aug 17, 2016 8:37 AM by Talha Valam

# Using IF statements for one field and using another field in the VIZ

Hi all,

I think this is an easy solution, but I just can't figure out how to go about doing this, so forgive the newbie question. I think what I'm trying to do can be done using IF statements, but can't find the logic to follow through.

Basically, I want to be able to crate a formula that says something like, “if the total sales to a customer are between 10k to 50k in 2015 then label that customer as Small/Medium/Large"

Then I want to use that customer classification to create a viewpoint that kind of looks like the spreadsheet below. Doesn't have to be exact..

After creating these "customer groupings", I'd like to use the Product Code field in the viz. I think that's where I'm having the trouble.

So my question is, what steps do I have to take to try and replicate the sample spreadsheet above?

Hopefully this makes sense. And I appreciate any help from the discussion boards.

Thanks!

• ###### 1. Re: Using IF statements for one field and using another field in the VIZ

See this.

• ###### 2. Re: Using IF statements for one field and using another field in the VIZ

Hi Luciano

Thanks for the quick response.

From your worksheet, it looks like sales of the PRODUCT is what you've categorized as large, medium, or small.

I actually want to categorize my CUSTOMERS as large, medium, and small. Then put that in the viz along with product code.

That way, the viz would ideally show ALL the min and max selling price for the products based on the type of customer that purchased it.

Does that make more sense?

• ###### 3. Re: Using IF statements for one field and using another field in the VIZ

I understood, but changing to another ways using LOD the result was the same.

You can test editing TAG field.

If {include [Customer]: Sum( [Sales] )} < 20000 then "Small"

Elseif {include [Customer]: Sum( [Sales] )} < 60000 then "Medium"

Else "Large"

End

//If {fixed [Customer]: Sum( [Sales] )} < 20000 then "Small"

//Elseif {fixed [Customer]: Sum( [Sales] )} < 60000 then "Medium"

//Else "Large"

//End

//If {exclude [Product Code], [Description]: Sum( [Sales] )} < 20000 then "Small"

//Elseif {exclude [Product Code], [Description]: Sum( [Sales] )} < 60000 then "Medium"

//Else "Large"

//End

• ###### 4. Re: Using IF statements for one field and using another field in the VIZ

Ah I see.

But if I remove the Customer field from the view, then the data automatically aggregates up to the product code. At that point, all I can see is the LARGE, because the aggregated sales are greater than 60,000. That's what I would like to avoid.

I don't want the customer field to affect the view. If I do put the customer field in there, and even if I hide it, then won't there be duplicate entries for products (one for small, one for medium, and one for large customers)?

In your workbook, I see one entry for Product Code 123 under medium, one under small, and one under large. I would like all of those data points to fit into one row for that product if possible. Otherwise, there would be a lot of manual manipulation.

• ###### 5. Re: Using IF statements for one field and using another field in the VIZ

LOD calcs works only with dimension in sheets. Customer is needed.

• ###### 6. Re: Using IF statements for one field and using another field in the VIZ

Even without using LOD? In your first example, you didn't use any LOD functions.

Because our company has thousands of products, and thousands of customers, there is a possibility of having thousands of duplicates for the most popular selling products.

Is there no other way?

• ###### 7. Re: Using IF statements for one field and using another field in the VIZ

Without LOD, i can't ignore product field to calculate total.

• ###### 8. Re: Using IF statements for one field and using another field in the VIZ

See this one.

Customer has to be present

1 of 1 people found this helpful
• ###### 9. Re: Using IF statements for one field and using another field in the VIZ

Trying creating the {Fixed [Customer]; Sum([Sales])} as a separate calc. Then using that in the if statement.

See [Customer Sales] & [S/M/L Cust] calculations and Planilha 1 tab for results.

• ###### 10. Re: Using IF statements for one field and using another field in the VIZ

This last version is with fixed customer

• ###### 12. Re: Using IF statements for one field and using another field in the VIZ

Hi Phillip,

I tried what you said and it looks like it worked. I'm going to run a few checks on my end to make sure it works with my dataset.

Can you help me understand the logic you used to come up with the solution. Why does creating a LOD as a separate calc, and then using that calc in an IF statement get the desired effect?

• ###### 13. Re: Using IF statements for one field and using another field in the VIZ

Actually, I think the real issue was that tag was being placed on the columns shelf as an aggregate dimension. IE: ATTR(TAG)

If you don't aggregate it, it should work as it was written.

The way I wrote the calculations out was just cleaner to me and helps me to understand the logic a bit better. But both calculations achieve a similar result. The structure of the if statements might place some of the customers into the different groups.

Edit: Looking closer at the results, the calculations are classifying the customers differently, and I think that is because of how specific the if statements I defined are.

1 of 1 people found this helpful
• ###### 14. Re: Using IF statements for one field and using another field in the VIZ

Thanks Phillip. I think I understand.

I still have a lot to learn about LOD's.

1 2 Previous Next