topic Re: Need your help to get a percentage (Current Year Total / Previous Year Total) in App Development

Need your help to get a percentage (Current Year Total / Previous Year Total)

aarnkalle — Mon, 09 Dec 2019 04:58:39 GMT

I need your help to get a percentage (Current Year Total / Previous Year Total) on a row in Qliksense Pivot.

Sum (Total Distinct {<ActPhysician, Record1>} (CY_Gross)) / Sum (Total Distinct {<ActPhysician, Record1>} (PY_Gross))

Re: Need your help to get a percentage (Current Year Total / Previous Year Total)

Taoufiq_Zarra — Mon, 09 Dec 2019 08:28:23 GMT

Hi,

you can use :

for the current year

=Sum ( {<Year = {"$(=Max(Year))"}>} total YOURINDICATOR)

Previous year

=Sum ( {<Year = {"$(=Max(Year)-1)"}>} total YOURINDICATOR)

cheers,

Re: Need your help to get a percentage (Current Year Total / Previous Year Total)

anushree1 — Mon, 09 Dec 2019 09:30:29 GMT

Please try:

num(Sum (Total Distinct {<ActPhysician, Record1>} (CY_Gross)) / Sum (Total Distinct {<ActPhysician, Record1>} (PY_Gross)),'##.##%')

Re: Need your help to get a percentage (Current Year Total / Previous Year Total)

aarnkalle — Mon, 09 Dec 2019 10:55:11 GMT

Thanks for your response. However it is not showing expected results.

Re: Need your help to get a percentage (Current Year Total / Previous Year Total)

Taoufiq_Zarra — Mon, 09 Dec 2019 12:44:31 GMT

sorry but, I can't see the problem.

can you share an extract from your data sets? and the requested outpout !

Re: Need your help to get a percentage (Current Year Total / Previous Year Total)

aarnkalle — Mon, 09 Dec 2019 12:46:48 GMT

Please see attached.

Re: Need your help to get a percentage (Current Year Total / Previous Year Total)

Taoufiq_Zarra — Mon, 09 Dec 2019 12:54:32 GMT

what is the formula used for[Actual 2018] and[Actual 2019]?

Re: Need your help to get a percentage (Current Year Total / Previous Year Total)

sunny_talwar — Mon, 09 Dec 2019 12:55:05 GMT

What is your expression for Actual 2018 and Actual 2019?

Re: Need your help to get a percentage (Current Year Total / Previous Year Total)

aarnkalle — Mon, 09 Dec 2019 13:00:42 GMT

Sum (Total Distinct {<ActPhysician, Record1>} (CY_Gross)) / Sum (Total Distinct {<ActPhysician, Record1>} (PY_Gross))

"Record1" having filter for Actual 2018 and Actual 2019 data.

"CY_Gross" is the value for Actual 2019 and "PY_Gross" is the value for Actual 2018.

Re: Need your help to get a percentage (Current Year Total / Previous Year Total)

sunny_talwar — Mon, 09 Dec 2019 13:09:34 GMT

Can you try without TOTAL?

Sum(DISTINCT {<ActPhysician, Record1>} CY_Gross) / Sum(DISTINCT {<ActPhysician, Record1>} PY_Gross)

Re: Need your help to get a percentage (Current Year Total / Previous Year Total)

aarnkalle — Mon, 09 Dec 2019 13:14:50 GMT

It is showing 0% for all rows !!!

In excel it is easy but not sure what we are missing in Qlik to get row wise %

ActPhysician	Actual 2018	Actual 2019	Expected Result
Ali A	355,923.40	1,074,888.14	302%

Re: Need your help to get a percentage (Current Year Total / Previous Year Total)

sunny_talwar — Mon, 09 Dec 2019 13:17:22 GMT

It might help if you are able to share a qvf document for us to see what you have

Re: Need your help to get a percentage (Current Year Total / Previous Year Total)

aarnkalle — Tue, 10 Dec 2019 04:29:53 GMT

Unfortunately, i can't share QVF file due to company policy.

Re: Need your help to get a percentage (Current Year Total / Previous Year Total)

aarnkalle — Tue, 10 Dec 2019 06:02:33 GMT

I just converted data from Pivot Table to "Table" in Qlik app and used below formula -

Sum (CY_Gross) / Sum (PY_Gross)

It works as per business requirements.

Thanks for your help.