Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
abhinavpathak17
Contributor
Contributor

Dividing a cell by other cells in a table in Qlikview

Hi All,

I am trying to calculate the % in my table.

My table looks like below

   

Final_StatusFinal_new_genderCount
SourcingFEMALE159
SourcingMALE246
Screening ClearedFEMALE174
Screening ClearedMALE227
Offer MadeFEMALE67
Offer MadeMALE51
OnboardFEMALE34
OnboardMALE56
Interview ClearedFEMALE41
Interview ClearedMALE36

The % field needs to be calculated which will be different for both Male and female. It's bit tricky to write how this field is calculated ,so I have attached the sample file.

Any help will be appreciated.

Thanks

8 Replies
Chanty4u
Champion III
Champion III

gen.PNGHi

is this?

Test:

load *, if(Final_new_gender='MALE',0,1) as Gender;

LOAD * INLINE [

    Final_Status, Final_new_gender, Count

    Sourcing, FEMALE, 159

    Sourcing, MALE, 246

    Screening Cleared, FEMALE, 174

    Screening Cleared, MALE, 227

    Offer Made, FEMALE, 67

    Offer Made, MALE, 51

    Onboard, FEMALE, 34

    Onboard, MALE, 56

    Interview Cleared, FEMALE, 41

    Interview Cleared, MALE, 36

];

Strighttable:

Dim: Final status

Exp1;sum({<Gender={0}>}Count)

Exp2:sum({<Gender={1}>}Count)

Exp3: column(2)/Column(1)    [check relative]

abhinavpathak17
Contributor
Contributor
Author

Hi,

This is not how I wanted.The table should come exactly as I have shown in excel file attached. '%'  is the new column which needs to be added in the table.

shiveshsingh
Master
Master

may be this?

abhinavpathak17
Contributor
Contributor
Author

Hi,

Sorry this is not what I was looking for. If you look at the file and see the '%' field calculation , I have to get those numbers and table should look exactly the same as in excel.

anagharao
Creator II
Creator II

Hi,

You could use range sum. Divide the table out for Female and male (since the range sum doesn't work as expected with two dimensions) and use the below formulae

Numerator :

SUM(TOTAL {<Final_new_gender={'FEMALE'}>}Count) - RANGESUM(ABOVE(SUM({<Final_new_gender={'FEMALE'}>}Count),1,ROWNO()))

Denominator :

SUM(TOTAL {<Final_new_gender={'FEMALE'}>}Count) - RANGESUM(ABOVE(SUM({<Final_new_gender={'FEMALE'}>}Count),2,ROWNO()))

Note:

- Order the table on the load order (or you could associate each status with a priority and order by that)

- Add a clause to not calculate when status is "Sourcing"

Hope this helps

abhinavpathak17
Contributor
Contributor
Author

Hi Anagha,

Could you please share a sample QVW. I tried your way but it didn't work out.

anagharao
Creator II
Creator II

Hi, i cannot share anything .. I'm at office

Can you share your QVW so that i can look into whats wrong

qliksus
Specialist II
Specialist II

Load the excel into QV and try the below

Dimension:

Final_Status , Final_new_gender (as named in the excel )

Expression:

(PICK( WildMatch(Final_new_gender,'*FEMALE*','*MALE*') ,  rangesum(below(total sum({<Final_new_gender={'FEMALE'}>}Count),0,100)) ,
rangesum(below(total sum({<Final_new_gender={'MALE'}>}Count),0,100))) /

(
PICK( WildMatch(Final_new_gender,'*FEMALE*','*MALE*') ,sum( total<Final_Status>  aggr(above(total sum({<Final_new_gender={'FEMALE'}>}Count)),Final_Status)),
sum( total<Final_Status>  aggr(above(total sum({<Final_new_gender={'MALE'}>}Count)),Final_Status))) +

PICK( WildMatch(Final_new_gender,'*FEMALE*','*MALE*') ,  rangesum(below(total sum({<Final_new_gender={'FEMALE'}>}Count),0,100)) ,
rangesum(below(total sum({<Final_new_gender={'MALE'}>}Count),0,100)))

)

)