conditional Aggr Expression

[#1]

Hi All,

I have a following expression

=sum(Aggr([Order Product Cost], OrderId, [Order Product Cost]))

I use this expression in straight table and I have [Product Name] as dimension so this gives me total cost by each product name that have been ordered.

Now I want to add an if condition in the above expression to only show those product names where orders have been cancelled

Data Model

OrderId may repeat multiple times becasue an order can have more then 1 products. and each order contains OrderStatusId

OrderStatusId 5 = cancelled.

Thanks

[marcus_sommer · Accepted solution]

Maybe this one:

=sum({< [OrderStatusId 5] = {'cancelled'}>} Aggr([Order Product Cost], OrderId, [Order Product Cost]))

- Marcus

[#3]

Thanks Marcus it worked after little tweak in your expression because my mistake.

My explanation was not that good.. so just updating my question that the Order Status field name is OrderStatusId not OrderStatusId 5 and OrderStatusId has values 1 to 5 and value 5 means cancelled

so I updated your expression with following and it worked.

=sum({< [OrderStatusId] = {5}>} Aggr([Order Product Cost], OrderId,[Order Product Cost]))