Variables in .txt file brought in script via include statement

[naziralala]

Hello All,

The following script is in an include statement:

Set eRevenue = if (([Travel Year]='$(vYearToday)' and  [Travel Month]=$(vMaxMonth)),

sum({<[Travel Year]={$(vMaxYear)},[Travel Month]={"=$(vMaxMonth)"}>}Revenue ),

if (([Travel Year]='$(vYearToday)'-1 and  [Travel Month]=$(vMaxMonth)),

sum({<[Travel Year]={$(vMaxYear)},[Travel Month]={"=$(vMaxMonth)"}>}Revenue ),

if (([Travel Year]='$(vYearToday)'-2 and  [Travel Month]=$(vMaxMonth)),

sum({<[Travel Year]={$(vMaxYear)},[Travel Month]={"=$(vMaxMonth)"}>}Revenue ),

sum({<[Travel Year]={$(vMaxYear)},[Travel Month]={"<=$(vMaxMonth)"}>}Revenue ))));

Can you advise what is the issue in this as it is not working?

Also, in same file I have script as below which is working in chart:

Set eRankRevenue = rank(eRevenue);

The same script is working in variables in charts.

Thanks,

Nazira

[sunny_talwar]

Script is running into an error or the expression is not working on the front end?

[trdandamudi]

Need more information as Sunny mentioned... it will help us to identify the issue, Also try eRevenue expressions individually and see if you are getting the expected results and that will give you a clear idea where it is going wrong if any.

[naziralala]

The same script is working in the chart...tried individually..

[ellenblackwell]

Not sure if this is the same problem, but I have experienced similar behavior when pulling expressions in from Excel. The problem with Excel is related to hard returns in the individual cells which contain the expression. Only the first line of the expression was coming in and being evaluated.

For example, the following expression is written for easy reading/understanding, and it works fine in a QV object:

=If([Product]='A', sum(Sales),
     if([Product]='B', sum(Sales)-sum(Taxes))
     )

However, for the full expression to come into QV from an Excel cell, the returns have to be removed:

=If([Product]='A', sum(Sales),  if([Product]='B', sum(Sales)-sum(Taxes)))

If the text wraps automatically, there is no problem. Hope this helps.

Regards

[naziralala]

Hello Ellen,

Can you please explain in more detail?

I have done in .txt file..how can we do the same in excel..and in

detail..if possible with an example.

Thanks in advance

Nazira

[sunny_talwar]

May be check this out: Managing Variables

[ellenblackwell]

Hello Nazira,

I have used, and would recommend, the same post that Sunny referred to in his latest response. If your requirement can be met using .txt, however, I would try to resolve the issue within your current process.

Depending on the issue, you may just need a 2nd equal sign in your SET statement. Below, I have implemented  the 2 suggestions I've made within your expression. Perhaps one of these options will work.

OPTION 1 - Remove hard returns from the expression in your .txt file:

Set eRevenue = if (([Travel Year]='$(vYearToday)' and  [Travel Month]=$(vMaxMonth)), sum({<[Travel Year]={$(vMaxYear)},[Travel Month]={"=$(vMaxMonth)"}>}Revenue ), if (([Travel Year]='$(vYearToday)'-1 and  [Travel Month]=$(vMaxMonth)),sum({<[Travel Year]={$(vMaxYear)},[Travel Month]={"=$(vMaxMonth)"}>}Revenue ),if (([Travel Year]='$(vYearToday)'-2 and  [Travel Month]=$(vMaxMonth)), sum({<[Travel Year]={$(vMaxYear)},[Travel Month]={"=$(vMaxMonth)"}>}Revenue ), sum({<[Travel Year]={$(vMaxYear)},[Travel Month]={"<=$(vMaxMonth)"}>}Revenue ))));

OPTION 2 - Add an '=' sign to the expression, so that it is evaluated when you bring it in

Set eRevenue = =if (([Travel Year]='$(vYearToday)' and  [Travel Month]=$(vMaxMonth)),

sum({<[Travel Year]={$(vMaxYear)},[Travel Month]={"=$(vMaxMonth)"}>}Revenue ),

if (([Travel Year]='$(vYearToday)'-1 and  [Travel Month]=$(vMaxMonth)),

sum({<[Travel Year]={$(vMaxYear)},[Travel Month]={"=$(vMaxMonth)"}>}Revenue ),

if (([Travel Year]='$(vYearToday)'-2 and  [Travel Month]=$(vMaxMonth)),

sum({<[Travel Year]={$(vMaxYear)},[Travel Month]={"=$(vMaxMonth)"}>}Revenue ),

sum({<[Travel Year]={$(vMaxYear)},[Travel Month]={"<=$(vMaxMonth)"}>}Revenue ))));

Let us know if one of these work!

[naziralala]

Hello Ellen,

The post recommended by Sunny works except in case as below:

ePYRevenue

'=if (([Travel Year]='$(vYearToday)' and  [Travel Month]=$(vMaxMonth)), sum({<[Travel Year]={$(vPreviousYear)},[Travel Month]={"=$(vMaxMonth)"}>}Revenue ), if (([Travel Year]='$(vYearToday)'-1 and [Travel Month]=$(vMaxMonth)), sum({<[Travel Year]={$(vPreviousYear)},[Travel Month]={"=$(vMaxMonth)"}>}Revenue ), if (([Travel Year]='$(vYearToday)'-2 and [Travel Month]=$(vMaxMonth)), sum({<[Travel Year]={$(vPreviousYear)},[Travel Month]={"=$(vMaxMonth)"}>}Revenue ), sum({<[Travel Year]={$(vPreviousYear)},[Travel Month]={"<=$(vMaxMonth)"}>}Revenue ))))

Please advise.

Thanks for all your help.

Nazira

[naziralala]

Hello Sunny,

The post recommended by you works except in case as below:

ePYRevenue

'=if (([Travel Year]='$(vYearToday)' and  [Travel Month]=$(vMaxMonth)), sum({<[Travel Year]={$(vPreviousYear)},[Travel Month]={"=$(vMaxMonth)"}>}Revenue ), if (([Travel Year]='$(vYearToday)'-1 and [Travel Month]=$(vMaxMonth)), sum({<[Travel Year]={$(vPreviousYear)},[Travel Month]={"=$(vMaxMonth)"}>}Revenue ), if (([Travel Year]='$(vYearToday)'-2 and [Travel Month]=$(vMaxMonth)), sum({<[Travel Year]={$(vPreviousYear)},[Travel Month]={"=$(vMaxMonth)"}>}Revenue ), sum({<[Travel Year]={$(vPreviousYear)},[Travel Month]={"<=$(vMaxMonth)"}>}Revenue ))))

Please advise.

Nazira