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: 
aarnkalle
Contributor III
Contributor III

How to get a percentage only on a selected fields (not null cases)

Hi

With reference to above subject, I am trying below formula in expressions but getting "Error in Expression".

    (Sum(if(Levels = 'Level*', (Gross),'NA'))) / (sum((if(Levels = 'Level*', total (Gross),'NA'))))

Please see the above and help me to identify where I am making mistake.

Thanks in advance.

Abhijit ARANKALLE.

 

 

1 Solution

Accepted Solutions
Anil_Babu_Samineni

Perhaps this?

Alt(Sum({<Levels = {'Level*'}>} Gross)/Sum({<Levels = {'Level*'}>} TOTAL Gross), 'NA')

Or, For me this is suffice

Alt(Sum({<Levels = {'Level*'}>} Gross)/Sum(TOTAL Gross), 'NA')

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

View solution in original post

11 Replies
Anil_Babu_Samineni

Perhaps this?

Alt(Sum({<Levels = {'Level*'}>} Gross)/Sum({<Levels = {'Level*'}>} TOTAL Gross), 'NA')

Or, For me this is suffice

Alt(Sum({<Levels = {'Level*'}>} Gross)/Sum(TOTAL Gross), 'NA')

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
aarnkalle
Contributor III
Contributor III
Author

Hi

Thanks a lot for your response. It working fine in total but when I am adding quarter fields in pivot - column, then I am getting incorrect results by quarter.

Thanks and Regards,

Abhijit ARANKALLE.

aarnkalle
Contributor III
Contributor III
Author

Only when there is only one dimension as Level. When you have a Quarter or Month dimension in column, then results are not perfect.
sunny_talwar

I see you already have a thread... would you be able to provide a sample with the issue and what you expect to see as the output from the sample provided?
aarnkalle
Contributor III
Contributor III
Author

Hi

Please see attached. The red one is current result and green colour is I am expecting.

Thanks and regards,

Abhijit ARANKALLE.

sunny_talwar

Try this

Sum(Gross)/Sum(TOTAL <EncDate.YearQuarter> Gross)
aarnkalle
Contributor III
Contributor III
Author

Hi

I tried this but it is taking percentage for NULL values also.

sunny_talwar

percentages for NULL values? really? Can you share a screenshot to show what you mean?
aarnkalle
Contributor III
Contributor III
Author

Hi

Please see attached file for details.

Thanks and Regards,

Abhijit ARANKALLE.