Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
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

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.

Highlighted
Specialist
Specialist

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.

Highlighted

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

Not applicable

Exclude Sales Orders based on Cost Category

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

Highlighted

Re: Exclude Sales Orders based on Cost Category

Hi,

     Can you hard code Cost Category?

Celambarasan

Highlighted
Not applicable

Re: Exclude Sales Orders based on Cost Category

Hi,

please see the attached example for help.

Good luck!

Rainer

Highlighted

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

Highlighted
Not applicable

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

Highlighted

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