topic Re: Result needed in front end in QlikView

Result needed in front end

Tue, 03 Dec 2013 18:18:56 GMT

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
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 .

Re: Result needed in front end

Michiel_QV_Fan — Tue, 03 Dec 2013 18:24:10 GMT

Use set analysis in your expression:

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

Re: Result needed in front end

Tue, 03 Dec 2013 18:33:10 GMT

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

Re: Result needed in front end

Michiel_QV_Fan — Tue, 03 Dec 2013 18:44:06 GMT

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

Re: Result needed in front end

Tue, 03 Dec 2013 18:49:25 GMT

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

Re: Result needed in front end

Michiel_QV_Fan — Tue, 03 Dec 2013 18:51:47 GMT

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

Re: Result needed in front end

Tue, 03 Dec 2013 19:22:58 GMT

Hi Vikram,

Please use the below expression

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

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

Re: Result needed in front end

Anonymous — Tue, 03 Dec 2013 19:29:29 GMT

Or without set:

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

Re: Result needed in front end

Anonymous — Tue, 03 Dec 2013 19:37:44 GMT

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)

Re: Result needed in front end

Tue, 03 Dec 2013 19:41:23 GMT

thks