topic conditional Aggr Expression in QlikView

conditional Aggr Expression

Wed, 06 Jul 2016 00:24:12 GMT

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

Re: conditional Aggr Expression

marcus_sommer — Wed, 06 Jul 2016 05:26:33 GMT

Maybe this one:

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

- Marcus

Re: conditional Aggr Expression

Wed, 06 Jul 2016 08:39:00 GMT

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]))