topic Re: Calculating Ratio: Script or UI? in QlikView

Calculating Ratio: Script or UI?

dafnis14 — Wed, 02 May 2018 16:20:00 GMT

Hi,

I need to show a monthly ratio based on records with a resolution of half an hour.

For each record, the ratio is calculated by ActualAvail/STDAvail.

In a chart I use Average for that metric, based on a period selection (Month, Year..)

I wanted to know whether I should calculate the ratio dynamically in the chart.

If so, what is the reason?

Thanks!

Re: Calculating Ratio: Script or UI?

duncanblaine — Thu, 03 May 2018 03:33:48 GMT

Hi Dafnis

Are you asking whether you should use the Ratio directly from your source (looks like Excel?) or calculate the Ratio in your chart?
If so, then I would say its best to calculate the Ratio in your chart using the data points you have so that it will adjust depending on your dimensions and filter selection.
If you use the Ratio from Excel, you could end up with incorrect values due the issues of aggregating ratios. Its always best to avoid pulling pre-aggregated data into Qlik, especially when you have the granular data values.

Hope this helps you.
If I've misunderstood your question, please let me know.

Re: Calculating Ratio: Script or UI?

dafnis14 — Thu, 03 May 2018 03:59:51 GMT

Hi Duncan,

Thanks for your input.

The data is pulled from a database.

So the ratio is calculated in the script.

Since the metrics are to be used in a dashboard, no drill down, it is more convenient to do the aggregation in the script.

I saw a difference of around 0.04% between the 2 methods (in the script or in a chart).

Just want to make sure that it's correct "arithmetically"..

Re: Calculating Ratio: Script or UI?

duncanblaine — Thu, 03 May 2018 08:03:00 GMT

Could it be that you have a number format that is affecting the decimal places, which might cause a different total when exported to Excel.

Re: Calculating Ratio: Script or UI?

duncanblaine — Thu, 03 May 2018 08:06:15 GMT

In answer to your original question:

There should be no difference arithmetically between calculating the Ratio in the UI or in script.

The only difference I can think of is the way the decimals are rendered in the UI, which is configured on the Number tab in the Object properties.

Re: Calculating Ratio: Script or UI?

dafnis14 — Thu, 03 May 2018 09:14:54 GMT

Thanks!