INCLUDE file for tables/graphs

[#1]

Hello, I have many table and graph expressions that share the same piece of code:

Region=P(Region),Report_Grouping=P(Report_Grouping),Business=P(Business), Business_OT_Top=P(Business_OT_Top),Business_OT=P(Business_OT),Business_OT_Chart=P(Business_OT_Chart),
Business_OT_HRMOR=P(Business_OT_HRMOR),Business_ICG_OT=P(Business_ICG_OT),Business_GF=P(Business_GF),[Sub-Business]=P([Sub-Business]),
[Sub-Business_ARR]=P([Sub-Business_ARR]),[Sub-Business_C&C]=P([Sub-Business_C&C]),[Sub-Business_CSIS]=P([Sub-Business_CSIS]),[Sub-Business_CSS]=P([Sub-Business_CSS]),
[Sub-Business_CTI]=P([Sub-Business_CTI]),[Sub-Business_EntOT]=P([Sub-Business_EntOT]),[Sub-Business_ExpMgt]=P([Sub-Business_ExpMgt]),[Sub-Business_Finance]=P([Sub-Business_Finance]),
[Sub-Business_HR]=P([Sub-Business_HR]),[Sub-Business_Legal]=P([Sub-Business_Legal]),[Sub-Business_NACO]=P([Sub-Business_NACO]),[Sub-Business_Risk]=P([Sub-Business_Risk]),
[Sub-Business_Level_2]=P([Sub-Business_Level_2]),[Sub-Business_Level_3]=P([Sub-Business_Level_3]),

Can I put this code in a central location so if a change needs to be made, can I just make it once? If this is possible, how do I reference the code within the Expression formula?

[#2]

Create a text file and inside the text file set the values

Set Region ='P(Region),Report_Grouping=P(Report_Grouping),Business=P(Business)';

Set Business_OT_Top = 'P(Business_OT_Top),Business_OT=P(Business_OT)';

let vConfigFile = 'Code.txt';

$(Include=project include files\$(vConfigFile))

This is one way, Another way you can put it in excell also

[#3]

------------------------------------------------------------------------------------------------------------------

ExpressionList:

LOAD

Variable,

Expression

FROM ..\Config Files\Expressions_V0.xls (biff, embedded labels, table is Sheet1$);

Let vNumRows = NoOfRows('ExpressionList');

for counter=1 to $(vNumRows)

Let vVariable = Peek('Variable', $(counter) -1, 'ExpressionList');

Let vExpression = Peek('Expression', $(counter) -1, 'ExpressionList');

Let $(vVariable) = Peek('Expression', $(counter) -1, 'ExpressionList');

next

-------------------------------------------------------------------------------------------------------

In your expressions $(Variable) Example : $(Region)

[Oleg_Troyansky]

The short answer is - save all the text into a variable and then reference the variable inside your Set Analysis, using $-sign expansion:

$(vMyConditions)

Besides that, what's the meaning of this long condition, where each field is equal to the set of possible values in the same field? This is identical to the default QlikView calculation, but it might hurt performance if any of those fields have many distinct values. I can't follow the point behind this condition...

[#5]

Thanks for your replies. I'm going with the variable route, which seems to work if it is the only variable in the expression. It doesn't work when there is another variable somewhere in the expression. My variable name is iPossible. I tried your syntax - $(iPossible), but I found that I had to put it in single quotes like this - '$(iPossible)'. To answer your question about setting each field to the set of possible values, we need these graphs to change dynamically with the user's selections. When using Set Analysis on the entire data set - {1} - the selections are ignored. Adding this code seemed to make the selections work.

Do you know why my second expression below may not be working? I can't figure it out. Thanks.

This one works:

Sum ({<New_Rating={'High Performers'},

'$(iPossible)',

NEW_TERM_TYPE={'VOL_ATTRITION'}>}Terms)

/

(if((Range='YTD') or (Range='Prior YTD'),avgDiv,

if((Range like 'Q*') and (mod(num#(date(Max(feeddate),'M')),3)=0),((num#(date(Max(feeddate),'M')))/num(mid(Range,2,1))),

if((Range like 'Q*') and (mod(num#(date(Max(feeddate),'M')),3)<>0),(mod(num#(date(Max(feeddate),'M')),3)),avgDiv

))))*12

/

(sum({<New_Rating={'High Performers'},

'$(iPossible)',

[Employee_Non-Employee]={'Employee'}>}TurnoverPopulationFlag)/

if((Range='YTD') or (Range='Prior YTD'),num#(date(Max(TOTAL feeddate),'M')),

if((Range like 'Q*') and (mod(num#(date(Max(feeddate),'M')),3)=0),(num#(date(Max(feeddate),'M')))/num(mid(Range,2,1)),

if((Range like 'Q*') and (mod(num#(date(Max(feeddate),'M')),3)<>0),mod(num#(date(Max(feeddate),'M')),3),avgDiv

))))

This one does NOT work:

Sum({1<Year={'$(vYear_1)'},
'$(iPossible)',
NEW_TERM_TYPE={'VOL_ATTRITION'}>}Terms)*12
/
(sum({1<Year={'$(vYear_1)'},
'$(iPossible)',
[Employee_Non-Employee]={'Employee'}>}TurnoverPopulationFlag))

[Oleg_Troyansky]

I'm very surprised by the need to enclose the variable in single quotes... Actually, based on everything I know, it shouldn't work... I recommend that you double check your results.

The only visible issue in the second expression is, once again, using single quotes for a numeric value of the Year.

Regarding your other explanation - what's the reason for using Set Analysis {1}, if you still need to enforce current selections? Why not use Set Analysis {$} and then explicitly disregard specific fields, using modifiers like these:

MyField1 =, MyField2=, ...

[#7]

Yes, removing the single quotes seems to work! However, in Edit Expression, all indications are that the expression is wrong - I get an "error in expression" message and the font colors change to black. In the editor, I'm used to seeing green and yellow font colors for expressions that have been written correctly.