Measures VS Calculated fields

[JuMo]

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

Opens in new window

PDF Download

Word Download

Excel Download

PowerPoint Download

Document Download

[SRA]

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

[JuMo]

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. ..

[Qrishna]

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

[rwunderlich]

"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

[JuMo]

OK, 1st message updated with sample data

Opens in new window

PDF Download

Word Download

Excel Download

PowerPoint Download

Document Download

[JuMo]

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 ....

[rwunderlich]

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