topic Re: Need help in dual function in QlikView

Need help in dual function

vikasshana — Thu, 14 Dec 2017 14:52:46 GMT

Hi,

I've a pivot table like below in my Qlikview app.

Test	Server	sum(sales1)	Sum(Sales)
A	1		50
	2		90
	3		100
B	23	60
	34	48
	54	56

I would like to sort the sum(sales) based on server dimension and I'm using below expression in the sort tab of the pivot table.

dual(Test='A',sum(sales))

And I'm getting the output as below.

Test	Server	sum(sales1)	Sum(Sales)
A	3		100
	2		90
	1		50
B	23	60
	34	48
	54	56

Now my requirement is I would like to sort both the 'Tests(A and B)' and I'm trying the below expression and is giving me the error message saying it is not possible.

dual(Test='A',sum(sales),

dual(Test='B',sum(sales1))

I would like to see output as below,

Test	Server	sum(sales1)	Sum(Sales)
A	3		100
	2		90
	1		50
B	23	60
	54	56
	34	48

Regards,

Vikas

Re: Need help in dual function

Anil_Babu_Samineni — Thu, 14 Dec 2017 15:09:27 GMT

May be

dual(Test='A' or Test='B',sum(sales))

Re: Need help in dual function

vikasshana — Thu, 14 Dec 2017 15:10:48 GMT

Sorry Anil, I just edited my question

Re: Need help in dual function

Anil_Babu_Samineni — Thu, 14 Dec 2017 15:19:22 GMT

Why 34 and 54 interchange?

Re: Need help in dual function

vikasshana — Thu, 14 Dec 2017 15:35:31 GMT

I want that sorted based on the highest value, so that's why they got interchanged.

Re: Need help in dual function

sunny_talwar — Thu, 14 Dec 2017 18:52:58 GMT

I see you have changed your requirement a little bit, so you don't want this any more?

Dimension

Test

=Aggr(Dual(Server, Sum(Sales)), Test, Server)

Expression

=If(Test = 'B', Sum(Sales))

=If(Test = 'A', Sum(Sales))

Sort tab

Make sure to sort numerically the second dimension by descending order

Re: Need help in dual function

vikasshana — Fri, 15 Dec 2017 08:48:50 GMT

No sunny, I'm using different expression sum(sales) and sum(sales1).

Re: Need help in dual function

sunny_talwar — Fri, 15 Dec 2017 09:56:02 GMT

How does it matter....Changed the data to this

LOAD * INLINE [

Test, Server, Sales, Sales1

A, 1, 50

A, 2, 90

A, 3, 100

B, 23, , 60

B, 34, , 48

B, 54, , 56

];

and use this

Dimension

Test

=Aggr(Dual(Server, Sum(Sales)+Sum(Sales1)), Test, Server)

Expression

=If(Test = 'B', Sum(Sales1))

=If(Test = 'A', Sum(Sales))