Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

CITY/MEMBER Dimension wise Cumulative %tile Calculation

Dear All,

i have table with CITY and MEMBER Dimension as below:

MEMBER CITY TURNOVER
AMUMBAI100
ADELHI200
BDELHI100
BMUMBAI300
BPUNE500
CDELHI50
CMUMBAI250
CPUNE600
DDELHI25
DMUMBAI50
DPUNE200
EMUMBAI25
EPUNE125
EDELHI225
FMUMBAI110
FPUNE150
FDELHI300

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%
D252.78%
C508.33%
B10019.44%
A20041.67%
E22566.67%
F300100.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
MUMBAI100
DELHI200

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%
MUMBAI100<what ever value as based of MUMBAI>
DELHI20041.67%

I am also attaching the sample QVW for ready reference.

Kindly help.

Thanks & Regards,

Jitendra K Vishwakarma

22 Replies
Not applicable
Author

Do anyone have any idea how to get this?

tresesco
MVP
MVP

Like in attached qvw ?

MK_QSL
MVP
MVP

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

Not applicable
Author

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%
D252.78%
C508.33%
B10019.44%
A20041.67%
E22566.67%
F300100.00%

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

Memberwise turnover and cumlative %tile of MUMBAI City

MEMBER Sum(TURNOVER) Cumlative %tile
835 100.00%
E252.99%
D508.98%
A10020.96%
F11034.13%
C25064.07%
B300100.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
DELHI20041.67%
MUMBAI10020.96%

Not applicable
Author

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%
E252.99%
D508.98%
A10020.96%
F11034.13%
C25064.07%
B300100.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%
D252.78%
C508.33%
B10019.44%
A20041.67%
E22566.67%
F300100.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
DELHI200
MUMBAI100

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
DELHI20041.67%
MUMBAI10020.96%

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

!

Not applicable
Author

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

image.png

tresesco
MVP
MVP

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

Not applicable
Author

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

PFA.

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