Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
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

Re: CITY/MEMBER Dimension wise Cumulative %tile Calculation

Do anyone have any idea how to get this?

MVP
MVP

Re: CITY/MEMBER Dimension wise Cumulative %tile Calculation

Like in attached qvw ?

MVP
MVP

Re: CITY/MEMBER Dimension wise Cumulative %tile Calculation

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

Re: CITY/MEMBER Dimension wise Cumulative %tile Calculation

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

Re: CITY/MEMBER Dimension wise Cumulative %tile Calculation

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

Re: CITY/MEMBER Dimension wise Cumulative %tile Calculation

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

MVP
MVP

Re: CITY/MEMBER Dimension wise Cumulative %tile Calculation

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

Not applicable

Re: CITY/MEMBER Dimension wise Cumulative %tile Calculation

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.

MVP
MVP

Re: CITY/MEMBER Dimension wise Cumulative %tile Calculation

PFA.

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