10 Replies Latest reply: Feb 14, 2012 6:57 AM by Rainer Filoda

# Exclude Sales Orders based on Cost Category

My data in QVD looks like below:

Sales Order    Cost Category     Values

1000               A                        100

1000               B                        200

1000               C                        300

1000               D                        400

2000               A                        100

2000               B                        200

2000               C                        300

If the Sales Order has Cost Category D then my report should not show Sales Order 1000. My report output should only show data for sales order 2000.

I tried following calculated dimension with following syntax but its not working:

= if(Cost Category = 'D', null(),Sales Order)

Any help is appreciated.

• ###### Exclude Sales Orders based on Cost Category

Hi,

Use Sales Order and Cost Category as Dimensions.

If([Cost Category] = 'D' AND [Sales Order] = 1000, 0, Sum(Values))

This is automatically remove the Cost Category D and Sales Order 1000 rows.

Regards,

Jagan.

• ###### Re: Exclude Sales Orders based on Cost Category

Hi,

use this expression like this,E function to remove the 1000 in Sales Order.

sum( {\$<Cost Category  = E({1<Sales Order ={‘1000’}>})>},{1<Cost Category ={'D'}>}) Values)

Regards,

Iyyappan.

• ###### Exclude Sales Orders based on Cost Category

Hi,

you can use Sales Order as dimension and following as expression

=Sum({<[Sales Order]-={\$(=Concat({<[Cost Category]={'D'}>}Distinct [Sales Order],','))}>} Values)

Hope it helps

Celambarasan

• ###### Exclude Sales Orders based on Cost Category

This is just a data example. Hence I cant hard code Sales Order no

• ###### Re: Exclude Sales Orders based on Cost Category

Hi,

Can you hard code Cost Category?

Celambarasan

• ###### Re: Exclude Sales Orders based on Cost Category

Hi,

=Sum({<[Sales Order]-={\$(=Concat({<[Cost Category]={'D'}>}Distinct [Sales Order],','))}>} Values)

Here i used Category D inside the Concat({<[Cost Category]={'D'}>}Distinct [Sales Order],',') you can add anything to this.

Celambarasan

• ###### Re: Exclude Sales Orders based on Cost Category

How can I add for both Cost Category C & D in set expression.

So show report if Cost Category C OR D is included in Sales Order Line Items

• ###### Exclude Sales Orders based on Cost Category

Hi,

You can add using comma between the string values.

=Sum({<[Sales Order]-={\$(=Concat({<[Cost Category]={'D','C'}>}Distinct [Sales Order],','))}>} Values)

Hope it helps

Celambarasan

• ###### Re: Exclude Sales Orders based on Cost Category

Hi,

please see the attached example for help.

Good luck!

Rainer

• ###### Re: Exclude Sales Orders based on Cost Category

Hi,

see this example.

You now able to select the values you need.

Good Luck!

Rainer