Result needed in front end

[#1]

Hi,

I have a table

Order	Order Type	Revenue
A	1	30
A	2	20
B	1	30
B	3	40
C	3	50
C	5	30
D	2	40
E	1	40

I want only those revenues which to be selected ,whose orders have order  type (1) or (1 and 2)

So resultant table should be

orders	order type	revenue	Header 4
A	1	30
A	2	20
B	1	30
E	1	40

I want this table to be generated in front end not back end .

[Michiel_QV_Fan]

Use set analysis in your expression:

sum({<Order Type = {'1','2'}>}Revenue)

[#3]

Hi Michiel van de Goor,

I initially tried it  , but i am also getting order D  , which i dont want in the result since it doesnt qualify the criteria of 1 or (1 and 2) as  order type in a order

[Michiel_QV_Fan]

Why not in the back end? This could be solved more easily with group by statements.

[#5]

Yes i did it in back end for sample  , but issue is that the real  data model is huge ,complex  and messy built by someone else and i dont want to touch it . 

So i want to do it by front end . i know this can be done in front end as well ,but somehow not able to crack the nut

[Michiel_QV_Fan]

Sorry, can't help you with this one. Good luck

[#7 · Accepted solution]

Hi Vikram,

Please use the below expression

Sum({<[Order Type]={1}>}Revenue) +

sum( {$<Order = P({<[Order Type]={1}>} Order),[Order Type]={2}>} Revenue)

[Anonymous]

Or without set:

if([Order Type]=1 or concat(total<Order> distinct [Order Type], '', [Order Type])=12, Revenue)

[Anonymous]

Nandkishor Pandirkar:

Solution is good, just your set can be a little cleaner:

Sum({<[Order Type]={1}> + <Order = P({<[Order Type]={1}>} Order), [Order Type]={2}>} Revenue)

[#10]

thks