CITY/MEMBER Dimension wise Cumulative %tile Calculation

[#1]

Dear All,

i have table with CITY and MEMBER Dimension as below:

MEMBER	CITY	TURNOVER
A	MUMBAI	100
A	DELHI	200
B	DELHI	100
B	MUMBAI	300
B	PUNE	500
C	DELHI	50
C	MUMBAI	250
C	PUNE	600
D	DELHI	25
D	MUMBAI	50
D	PUNE	200
E	MUMBAI	25
E	PUNE	125
E	DELHI	225
F	MUMBAI	110
F	PUNE	150
F	DELHI	300

i have make table which show Memberwise Turnover along with Cumulative %tile ( i have used relative option with full accumulation)

CITY Selected : DELHI

MEMBER	Sum(TURNOVER)	Cumlative %tile
	900	100.00%
D	25	2.78%
C	50	8.33%
B	100	19.44%
A	200	41.67%
E	225	66.67%
F	300	100.00%

In the Above table you can see Member 'A' have '41.67%'  on DELHI City.

Now below is my requirement.

suppose select a Member 'A' then i want  to see CITY wise Turnover of a MEMBER like below:

CITY	Sum(TURNOVER)
	300
MUMBAI	100
DELHI	200

In the above table i want to added a more column name 'Cumulative' which show the Member's Cumulative position which we seen on above table i.e. 41.67% . the output will as below:

CITY	Sum(TURNOVER)	Cumlative
	300	0.00%
MUMBAI	100	<what ever value as based of MUMBAI>
DELHI	200	41.67%

I am also attaching the sample QVW for ready reference.

Kindly help.

Thanks & Regards,

Jitendra K Vishwakarma

[#2]

Do anyone have any idea how to get this?

[tresesco]

Like in attached qvw ?

[MK_QSL]

Create Straight Table

Dimension

City

Enable Conditional

GetSelectedCount(MEMBER)>0

MEMBER

Enable Conditional

GetSelectedCount(CITY)>0

Expression

SUM(TURNOVER)/SUM(TOTAL TURNOVER)

I assume that you know how to use Number Tab to show values in Integer and in % Format..

[#5]

No. That is not write. i want in below way

CITY Selected : DELHI ( Focus ON 'A' Member)

Memberwise turnover and cumlative %tile of DELHI City

MEMBER	Sum(TURNOVER)	Cumlative %tile
	900	100.00%
D	25	2.78%
C	50	8.33%
B	100	19.44%
A	200	41.67%
E	225	66.67%
F	300	100.00%

CITY Selected : MUMBAI ( Focus ON 'A' Member)

Memberwise turnover and cumlative %tile of MUMBAI City

MEMBER	Sum(TURNOVER)	Cumlative %tile
	835	100.00%
E	25	2.99%
D	50	8.98%
A	100	20.96%
F	110	34.13%
C	250	64.07%
B	300	100.00%

Now i want a table which show CITYwise Turnover of a Selected MEMBER ONLY.  and the calculation what we have seen above in the table, but want in CITY dimension table. 

SELECTED MEMBER = 'A'

CITY	Sum(TURNOVER)	Cumlative
	300
DELHI	200	41.67%
MUMBAI	100	20.96%

[#6]

Dear Sir,

My requirement is i want to show the CITY wise TURNOVER of a particular Member Only. the the Cumlative % should be get calculated on TURNOVER on the base of all the MEMBER of that CITY and then get the %tile of Selected MEMBER Only.

Like MUMBAI city MEMBER Cumulative value are as below: ( this table are just show the calculation purpose)

MEMBER	Sum(TURNOVER)	Cumlative %tile
	835	100.00%
E	25	2.99%
D	50	8.98%
A	100	20.96%
F	110	34.13%
C	250	64.07%
B	300	100.00%

DELHI city MEMBER Cumulative Value are as below:  ( this table are just show the calculation purpose)

MEMBER	Sum(TURNOVER)	Cumlative %tile
	900	100.00%
D	25	2.78%
C	50	8.33%
B	100	19.44%
A	200	41.67%
E	225	66.67%
F	300	100.00%

above table are for reference purpose what calculation i required which value i needs to show in below table..

Now below is my MAIN RESULT Table. When i select a MEMBER Like 'A'

then the RESULT Table will show CITY wise TURNOVER (Which i know how to do. No Issue in that) as below:

For example i have selected MEMBER 'A'

CITY	Sum(TURNOVER)
	300
DELHI	200
MUMBAI	100

Now if you see above two Reference(just to show the calculation) table , MEMBER 'A' have %tile value in DELHI '41.67%' and in MUMBAI have '20.96%'

these Value i also want to show in the same manner like this:

CITY	Sum(TURNOVER)	Required Value
	300
DELHI	200	41.67%
MUMBAI	100	20.96%

i have also adding a latest QVW Please consider the DARK RED Border for more detail

!

[#7]

Hi. There is some thing wrong in QVW i am sending you the screenshot also for same. i have detach some table which not working

[tresesco]

This is something that should to be dealt in the script per se. Could you share your sample data inline?

[#9]

I am sharing you with sample(dummy with only selected dimension)  Data, but my original data is very huge in size and have more dimension. this is i have share to just show the result what i want. Its also possible that this same concept may  be use in other dimension as well adhoc basis.

[tresesco]

PFA.

You have to adjust accordingly when it comes to other dimensions. Don't be worried about the syn key.