Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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.
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
This is just a data example. Hence I cant hard code Sales Order no
Hi,
Can you hard code Cost Category?
Celambarasan
Hi,
please see the attached example for help.
Good luck!
Rainer
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
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
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