topic Re: Measures VS Calculated fields in App Development

Measures VS Calculated fields

JuMo — Thu, 13 Feb 2025 09:15:30 GMT

Hello

I am preparing a new app. My row data will be a series of gSheet (one by geographical site) with a series of KPI (real value and target value) similar to what we have hereunder.

The product owner expects me to produce a dashboard with a geographical filter (they shall be able to see the data at site level, consolidated at country and region level) and a time filter (they shall be able to select a month)

For each possibility of these filters and for each KPI in the file, in the dashboard, I shall display:

The monthly real value
The monthly target value
The difference between monthly real value and monthly target value
The YTD real value
The YTD target value
The difference between YTD real value and YTD target value
A colour according if the YTD real value is higher than the YTD target value
A forecast of the YTD value EOY
A trend (a ratio between YTD real value & YTD target value) VS (a ratio between YTD real value & YTD target value) for previous month

Shall I create a measure for each of them and for each KPI?

Shall I pre-calculate all the possibilities in the loading script? (In that case, how should I do it ?)

I am a bit concerned by the performance since: we have a lot of sites and a huge number of KPI (an of course some sites have different KPI)

[EDIT]

Sample data added.

- IRL, data are in separated GSheets, not in the same Excel (but thats not a problem)

- I have much more KPI and Area

- All the columns are not present in all the files

Re: Measures VS Calculated fields

SRA — Wed, 12 Feb 2025 10:39:48 GMT

Hi,

You can create a measure for each KPI (Real, Target, Difference, Real YTD, Target YTD, Difference YTD..). DO NOT create a measure for each possiblity of selection (region country...). Qlik is here to help you aggregate & analyse your KPIs according to all dimensions of your data model. If your model is well designed, performance should not be one issue.

Regards

Re: Measures VS Calculated fields

JuMo — Wed, 12 Feb 2025 11:43:29 GMT

Hello,

Indeed, I did not plan to create a measure for each possibility and I plan to make the aggregation according to my dimensions (time and geography)

But even if a create a measure just for each KPI x (Real, Target, Difference, Real YTD, Target YTD, Difference YTD..); having 50 KPI, I will need 500 measures. That's why I was wondering if it isn't better to have those variations (Real, Target, Difference, Real YTD, Target YTD, Difference YTD..) calculated in the loading script. However, I dont know how to do it. ..

Re: Measures VS Calculated fields

Qrishna — Wed, 12 Feb 2025 14:27:20 GMT

please put the same data in an excel sheet so we can play around with it and come up with a solution. Thnx

Re: Measures VS Calculated fields

rwunderlich — Wed, 12 Feb 2025 16:46:49 GMT

"Shall I create a measure for each of them and for each KPI?"

Not for each KPI. Use Crosstable load to convert the KPI Names in to dimension values. So your table becomes something like:

Date, KPI, Real, Target
31 Jan, A, 25, 20
31 Jan, B, 25, 18

etc

-Rob

Re: Measures VS Calculated fields

JuMo — Thu, 13 Feb 2025 09:42:27 GMT

OK, 1st message updated with sample data

Re: Measures VS Calculated fields

JuMo — Thu, 13 Feb 2025 09:45:13 GMT

The problem here is all my KPI shall concolidate the same way. Some are simple sums, other are monthly products, others are overall ratio, others are min/max, others are average ....

Re: Measures VS Calculated fields

rwunderlich — Fri, 14 Feb 2025 13:55:30 GMT

I would recommend pre calculating the value in the script, but use a common field for the result. For example, "Difference" regardless of the type.

-Rob