topic Re: P() realated Query in QlikView

P() realated Query

Tue, 13 Oct 2015 11:33:17 GMT

Hi,

i want to show the TOTAL SUM of Turnover Member, City wise for only those Member, City which were there in Q2.

i have created a dummy table which is as below :

MEMBER	QUARTER	CITY	TURNOVER
RAJESH	Q2	DELHI	3000
RAJESH	Q2	MUMBAI	5000
AMIT	Q2	MUMBAI	4000
SURESH	Q2	DELHI	3000
RAJESH	Q3	MUMBAI	3000
RAJESH	Q3	DELHI	2000
AMIT	Q3	MUMBAI	4000
AMIT	Q3	DELHI	1000
SURESH	Q3	DELHI	5000
SURESH	Q3	MUMBAI	3000

OUTPUT what i excpecting

MEMBER	CITY	TOTAL TURNOVER
RAJESH	DELHI	5000
RAJESH	MUMBAI	8000
AMIT	MUMBAI	8000
SURESH	DELHI	8000

The above output is showing only those Member and City Turnover those were present in both Quarter.

i have tried below mention SET Analysis

sum( {$<CITY=P({$<QUARTER={'Q2'}>} CITY )>} TURNOVER)

but this SET Analysis is working proper only when i selected a Particular Member only.

Please find attacehed Dummy Excel table for same.

Re: P() realated Query

Tue, 13 Oct 2015 11:39:06 GMT

Adding the QVW also for ready reference,

Re: P() realated Query

sunny_talwar — Tue, 13 Oct 2015 11:44:39 GMT

One way would be to add a concatenate column in the script:

Table:

LOAD *,

MEMBER & '|' & CITY as KEY;

LOAD * Inline [

MEMBER, QUARTER, CITY, TURNOVER

RAJESH, Q2, DELHI, 3000

RAJESH, Q2, MUMBAI, 5000

AMIT, Q2, MUMBAI, 4000

SURESH, Q2, DELHI, 3000

RAJESH, Q3, MUMBAI, 3000

RAJESH, Q3, DELHI, 2000

AMIT, Q3, MUMBAI, 4000

AMIT, Q3, DELHI, 1000

SURESH, Q3, DELHI, 5000

SURESH, Q3, MUMBAI, 3000

];

and then using this expression:

=Sum({<KEY = p({<QUARTER = {'Q2'}>})>}TURNOVER)

Output:

Re: P() realated Query

Tue, 13 Oct 2015 11:49:35 GMT

Thanks for you active response and appreciated ur effort

I dont want to make a KEY of Member and City field because my data have many others fields too and huge in size also. for understanding purpose only i share only relevant fields. and in future if i required to same things on different combination also.

Thanks & Regards,

Jitendra K Vishwakarma

Re: P() realated Query

jonathandienst — Tue, 13 Oct 2015 12:06:49 GMT

Do you have a unique identifier field in your data set?. I think this will be necessary for the type of set expression require (and that I have in mind)

Re: P() realated Query

priyarane — Tue, 13 Oct 2015 12:42:09 GMT

Hi Jitendra,

Try this approach,

1. add calculated dimension for City - =if(QUARTER='Q2',CITY)

2. Expression : aggr(sum(TURNOVER),MEMBER,CITY)