topic Re: CITY/MEMBER Dimension wise Cumulative %tile Calculation in QlikView

CITY/MEMBER Dimension wise Cumulative %tile Calculation

Tue, 30 Jun 2015 05:00:47 GMT

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

Re: CITY/MEMBER Dimension wise Cumulative %tile Calculation

Tue, 30 Jun 2015 05:56:45 GMT

Do anyone have any idea how to get this?

Re: CITY/MEMBER Dimension wise Cumulative %tile Calculation

tresB — Tue, 30 Jun 2015 06:21:38 GMT

Like in attached qvw ?

Re: CITY/MEMBER Dimension wise Cumulative %tile Calculation

MK_QSL — Tue, 30 Jun 2015 06:29:09 GMT

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

Re: CITY/MEMBER Dimension wise Cumulative %tile Calculation

Tue, 30 Jun 2015 06:32:04 GMT

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%

Re: CITY/MEMBER Dimension wise Cumulative %tile Calculation

Tue, 30 Jun 2015 06:57:22 GMT

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

Re: CITY/MEMBER Dimension wise Cumulative %tile Calculation

Tue, 30 Jun 2015 07:04:57 GMT

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

Re: CITY/MEMBER Dimension wise Cumulative %tile Calculation

tresB — Tue, 30 Jun 2015 07:06:02 GMT

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

Re: CITY/MEMBER Dimension wise Cumulative %tile Calculation

Tue, 30 Jun 2015 07:14:48 GMT

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.

Re: CITY/MEMBER Dimension wise Cumulative %tile Calculation

tresB — Tue, 30 Jun 2015 08:12:21 GMT

PFA.

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

Re: CITY/MEMBER Dimension wise Cumulative %tile Calculation

MK_QSL — Tue, 30 Jun 2015 08:25:48 GMT

Create a Straight Table

Dimension

CITY

Expressions

1)SUM(TURNOVER)

2)SUM(TURNOVER)/SUM({1}TOTAL <CITY> TURNOVER)

Go to number tab to make 1st Expression as Integer and 2nd as %

Re: CITY/MEMBER Dimension wise Cumulative %tile Calculation

Tue, 30 Jun 2015 08:29:28 GMT

This is wrong .. this is not what i want..

CITY	Sum(TURNOVER)	SUM(TURNOVER)/SUM({1}TOTAL <CITY> TURNOVER)
	300	100.00%
MUMBAI	100	35.02%
DELHI	200	64.98%

Re: CITY/MEMBER Dimension wise Cumulative %tile Calculation

MK_QSL — Tue, 30 Jun 2015 08:31:25 GMT

If you select A what should be the answer? Please suggest...

Re: CITY/MEMBER Dimension wise Cumulative %tile Calculation

Tue, 30 Jun 2015 08:32:04 GMT

this is also wrong what i dont want

CITY	Sum(TURNOVER)	SUM(TURNOVER)/SUM({1}TOTAL <CITY> TURNOVER)
	300	9.06%
MUMBAI	100	11.98%
DELHI	200	22.22%

Re: CITY/MEMBER Dimension wise Cumulative %tile Calculation

Tue, 30 Jun 2015 08:39:04 GMT

below is screen shot what i expect the answer the Cumlative % is not CITY wise cumulative%, it is actually in city wise cumulative % of Memberwise and if i selected 'A' then it show as below.

the below first two table is showing the position on Members in DELHI and MUMBAI separately . A position is at 41.67% in DELHI and in MUMBAI 20.96%

These two table is just to show what i am calculating value which i requried to show when i just see last third.

the third table is show result when i select 'A'

Re: CITY/MEMBER Dimension wise Cumulative %tile Calculation

MK_QSL — Tue, 30 Jun 2015 08:48:04 GMT

Like this?

Re: CITY/MEMBER Dimension wise Cumulative %tile Calculation

Tue, 30 Jun 2015 08:51:05 GMT

Yes Right.. but you have done using load script. which don't want ... i am trying to do by set analysis only. because my original data is huge in size and date,year,month and many more dimension also....

Re: CITY/MEMBER Dimension wise Cumulative %tile Calculation

Tue, 30 Jun 2015 08:53:52 GMT

Hi Jitendra,

I have loaded your given data in QlikView by using Excel.

Then Create one filter on City and create on table 1 which shows:

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%

Now I have clone this table and created table 2. and in expression i have written:

sum({1<MEMBER=p(MEMBER)>}TURNOVER)/Sum({1<MEMBER=p(MEMBER)>}total TURNOVER)*100

So when you click on member,in table 1,table 2 will show citywise cumulative %.

Re: CITY/MEMBER Dimension wise Cumulative %tile Calculation

Tue, 30 Jun 2015 09:00:35 GMT

No.. that is not my requirment

Re: CITY/MEMBER Dimension wise Cumulative %tile Calculation

Tue, 30 Jun 2015 09:09:53 GMT

I think,this is what you want.