Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
The #1 reason QlikView customers adopt Qlik Sense is a desire for a modern BI experience. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
MVP & Luminary
MVP & Luminary

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.

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.

Highlighted

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

Highlighted
Not applicable

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

Highlighted

Hi,

     Can you hard code Cost Category?

Celambarasan

Highlighted
Not applicable

Hi,

please see the attached example for help.

Good luck!

Rainer

Highlighted

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

Highlighted
Not applicable

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

Highlighted

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