P() realated Query

[#1]

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.

[#2]

Adding the QVW also for ready reference,

[sunny_talwar]

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:

[#4]

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

[jonathandienst]

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)

[priyarane]

Hi Jitendra,

Try this approach,

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

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