Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 .
Hi Vikram,
Please use the below expression
Sum({<[Order Type]={1}>}Revenue) +
sum( {$<Order = P({<[Order Type]={1}>} Order),[Order Type]={2}>} Revenue)
Use set analysis in your expression:
sum({<Order Type = {'1','2'}>}Revenue)
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
Why not in the back end? This could be solved more easily with group by statements.
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
Sorry, can't help you with this one. Good luck
Hi Vikram,
Please use the below expression
Sum({<[Order Type]={1}>}Revenue) +
sum( {$<Order = P({<[Order Type]={1}>} Order),[Order Type]={2}>} Revenue)
Or without set:
if([Order Type]=1 or concat(total<Order> distinct [Order Type], '', [Order Type])=12, Revenue)
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)
thks