Hi,
I have a report which includes many metrics, then a percentage average of how many of the metrics have been met. I then need to include historical data of how many metrics were met in previous months, however there's too much data involved to have all the historical line items.
What I'd like to do is have my script load previous months' metric averages from a .qvd file, add the new month's metric averages and save back to the .qvd file. The formulae for the metrics are quite long and have been defined in the Variable Overview (they also reference other formulae from other variables using Dynamic Aggregation). Is there a way to pass the values (not the formulae themselves) to the script when it's re-run? I've been trying to use variations on the below, though with no success.
DivisionList:
LOAD * INLINE [
Division
Div1
Div2
Div3
];
Metrics:
LOAD Division,
$(v_Metric1) AS Metric1
RESIDENT DivisionList;
STORE * FROM Metrics INTO metrics.qvd;
When I run the above it script errors with the below message:
Syntax error, missing/misplaced FROM:
Metrics:
LOAD Division,
=only({$<FormulaDesc={'Metric1a'}>}(Formula)) + =only({$<FormulaDesc={'Metric1b'}>}(Formula)) AS Metric1
RESIDENT DivisionList
Instead of bringing through the values it appears to be opening out the Variable Overview formulae that reference the Dynamic Aggregation.
Is what I'm trying to do possible? If so, how, and if not, is there a better way to export the values I need?
(I also need to filter each metric by the division, but I haven't got as far as that yet…)
Any help would be appreicated!
Thanks,
Tim