topic Re: How to get a percentage only on a selected fields (not null cases) in App Development

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

aarnkalle — Thu, 20 Dec 2018 08:05:11 GMT

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.

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

Anil_Babu_Samineni — Thu, 20 Dec 2018 10:19:08 GMT

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')

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

aarnkalle — Thu, 20 Dec 2018 10:45:34 GMT

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.

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

aarnkalle — Thu, 20 Dec 2018 12:51:06 GMT

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

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

sunny_talwar — Thu, 20 Dec 2018 13:03:23 GMT

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?

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

aarnkalle — Thu, 20 Dec 2018 13:08:49 GMT

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

Thanks and regards,

Abhijit ARANKALLE.

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

sunny_talwar — Thu, 20 Dec 2018 13:18:56 GMT

Try this

Sum(Gross)/Sum(TOTAL <EncDate.YearQuarter> Gross)

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

aarnkalle — Sun, 23 Dec 2018 04:27:29 GMT

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

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

sunny_talwar — Sun, 23 Dec 2018 16:35:15 GMT

percentages for NULL values? really? Can you share a screenshot to show what you mean?

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

aarnkalle — Mon, 24 Dec 2018 04:42:38 GMT

Please see attached file for details.

Thanks and Regards,

Abhijit ARANKALLE.

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

aarnkalle — Mon, 24 Dec 2018 05:11:43 GMT

I cracked it finally, when I used below formula to fetch quarter wise details.

Dual(Year([EncDate])&'-Q'&Num(Ceil(Num(Month([EncDate]))/3)),QuarterStart([EncDate]))

For percentage, I have used below formula -

Sum(Gross)/Sum(TOTAL <[EncDate.autoCalendar.YearQuarter]> Gross)

Also I have loaded only specific AcCodes while loading data instead of all AcCodes so as to avoid NULL values.

Hope this is helpful to all.

Thanks and Regards,

Abhijit ARANKALLE.

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

sunny_talwar — Mon, 24 Dec 2018 12:09:30 GMT

Awesome