how to use below expression in the scripting

[divianjani]

Hi All,

Could you please help me,I have an straight table expression like this,
if(Metrics='Approval rate' or Metrics='Activation Rate',num(Only({<Year={'=max(Year)'},Scenario={'Actual'},
Year={$(=max(Year))},MonthNum={$(=max({<Year={$(=max(Year))}>}MonthNum))}>}Value),'#,##0%'),
Only({<Year={'=max(Year)'},Scenario={'Actual'},Year={$(=max(Year))},MonthNum={$(=max({<Year={$(=max(Year))}>}MonthNum))}>}Value)); if(Metrics='Approval rate' or Metrics='Activation Rate',num(Only({<Year={'=max(Year)'},Scenario={'Actual'},
Year={$(=max(Year))},MonthNum={$(=max({<Year={$(=max(Year))}>}MonthNum))}>}Value),'#,##0%'),
Only({<Year={'=max(Year)'},Scenario={'Actual'},Year={$(=max(Year))},MonthNum={$(=max({<Year={$(=max(Year))}>}MonthNum))}>}Value));

and I need put this expression in the scripting , can any one show me how to do?

[Gysbert_Wassenaar]

You can't use set analysis in the script. So your expression can't be put in the script.

[marcus_sommer]

I think you will need to create a consolidation-table with an aggregation load, something like this:

consolidation:

Load

Metrics, Scenario, Year, MonthNum, sum(Value) as Value

Resident xyz

Group By Metrics, Scenario, Year, MonthNum;

If these fields didn't belong to one table you need to match them before maybe per mapping or joining. To consider is further if a filtering of those data to the max. Year is needed then often it's best to join pre-calculations from script with gui-calculations instead of doing it solely there or there.

- Marcus

[gsbeaton]

Hi Anjali R,

You can't just transpose an expression like that, especially a dynamic one, straight into the script.

The scripts in themselves look acceptable.  If your application is running slowly, or maintenance is becoming an overhead, then it is worthwhile optimising and looking for performance improvements.

A couple of things you could consider:

• Calendar flags, to make selecting current year, max year etc quicker (see Jason Pierce's excellent post here:Calendar with AsOf Flags, Compare Easter to Easter)
• Remove IF statements.  IFs are memory intensive because QlikView evaluates all expressions in the IF before processing the condition.  You can often use the PICK() or MATCH functions to do the same thing as IF without the extra ovverhead, eg PICK(MATCH([field],'Approval Rate', Activation Rate),vExpression1,vExpression2)

Hope that gives you some ideas.

George