Qlik Community

Qlik Sense Advanced Authoring

Discussion board where members can learn more about Qlik Sense Advanced Authoring.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

how to redistribute rows into other rows

Table1:

columnname1 Columnname2

v                                     1

w                                   2

x                                    4

y                                    8

z                                  30

Suppose i have Filterpane of  Dimension columnname1, if i click on  Z or filter Z, then  columnname2 corresponding column z will be redistributed to other rows in their respective ratios and add with their respective columns.

eg suppose I filter  on Z then  result will be like

columnname1 Columnname2    colmnname3

v                                     1                          (  (1/(1+2+4+8))*30)+1=3

w                                   2                            (  (2/(1+2+4+8))*30)+2=6

x                                    4                            (  (4/(1+2+4+8))*30)+4=12

y                                    8                            (  (8/(1+2+4+8))*30)+8=24

 

remove z if only z is filtered ,if 2 columns are selected then sum ofcolumnname2  corresponding to filterd columns will redistributed into the remaining.

eg:

if i filtered y and z then 38 will reditributed in their remaining rows w.r.t their ratios.

 

 

 

 

1 Reply
Highlighted
Partner
Partner

You need a formula like this for the final column (I took the liberty of shortening the names of columns in the data...):

Sum({<col1=1::col1-$::col1>} col2) + (Sum({<col1=1::col1-$::col1>} col2) / Sum({<col1=1::col1-$::col1>} total col2)) * Sum(total col2)

Instead of this funky "col1=1::col1-$::col1" syntax you could use the element function E() and "col1=E(col1)" which looks nicer, but can be potentially influenced by selections in other fields than just col1.

If you need any explanation on how this works let me know 😉