Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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.

10 Replies
jagan
Luminary Alumni
Luminary Alumni

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.

v_iyyappan
Specialist
Specialist

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.

CELAMBARASAN
Partner - Champion
Partner - Champion

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

Not applicable
Author

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

CELAMBARASAN
Partner - Champion
Partner - Champion

Hi,

     Can you hard code Cost Category?

Celambarasan

Not applicable
Author

Hi,

please see the attached example for help.

Good luck!

Rainer

CELAMBARASAN
Partner - Champion
Partner - Champion

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

Not applicable
Author

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

CELAMBARASAN
Partner - Champion
Partner - Champion

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