Store an expression with set analysis so to reuse it in different charts

[cicciput]

I often need to show the same expression with set analysis in 2 or more different charts, maybe because the first is a pie chart for the whole selected period, the second is a line chart per week, the third is a straight table per product and so on...

Every time I edit one expression in one place I have to struggle to find all other occurrences for the results to be consistent.

Is there a way to store these in a single place -- e.g. load script -- and recall them when needed?

Of course readability matters -- even more to me, I indent all expressions on several lines, add comments and so on, this saves me time later when I have to understand the code -- so being forced to use long unreadable oneliners is a no-no

Thanks for sharing your best practice

[sunny_talwar]

See if this is what you want

http://www.qlikfix.com/2011/09/21/storing-variables-outside-of-qlikview/

[bhaskar_sm]

Hi,

you can maintain your expression and label in one excel file and evaluate your expressions Loaded from an excel file directly in your chart.(PFB referance post)

Loading Expressions From File - WITHOUT creating variables in Script

Regards,

Bhasker Kumar

[cicciput]

Thanks Sunny and Bhasker,

honestly I do not need to store anything outside the file -- every report has its own data structure -- I understand the concept could be reverse engineered and reused locally,

Problem is this kills my need of readability

If you could choose, what would you prefer of the following?

Sum({<

  AIS_Lot.IsSold = {yes},

  AIS_Account.IsBCA = {no},

  AIS_Sale.Category = {LOL}

>} AIS_Lot.DCF)

/

Sum({<

  AIS_Sale.Category = {LOL}

>} AIS_Lot.DCF)

or

Sum({<AIS_Lot.IsSold={yes},AIS_Account.IsBCA={no},AIS_Sale.Category=LOL}>}AIS_Lot.DCF)/Sum({<AIS_Sale.Category = {LOL}>}AIS_Lot.DCF)

Consider this example is quite simple, without functions or variables in set analysis...

[bhaskar_sm]

Hi,

i will prefer first one:

Sum({<

  AIS_Lot.IsSold = {yes},

  AIS_Account.IsBCA = {no},

  AIS_Sale.Category = {LOL}

>} AIS_Lot.DCF)

/

Sum({<

  AIS_Sale.Category = {LOL}

>} AIS_Lot.DCF)

[cicciput]

Me too

so does anyone know if there's a way to reuse this expression in different charts (in the same app)?

Sort of:

vConvRateExpr = 'Sum({<

  AIS_Lot.IsSold = {yes},

  AIS_Account.IsBCA = {no},

  AIS_Sale.Category = {LOL}

>} AIS_Lot.DCF)

/

Sum({<

  AIS_Sale.Category = {LOL}

>} AIS_Lot.DCF)';

More or less all programming languages allow you to do this.

[sunny_talwar]

I am not sure I understand the concern... I might be missing something... but let's look at this example...

1) Excel with two expressions you gave

You don't have to expand the cell size, but because you love viewing... I thought you would want to expand

2) Script

Temp_Variables:

LOAD Name,

    Expression,

    Comment

FROM

[300576.xlsx]

(ooxml, embedded labels, table is Sheet1);

// Create variables

for i = 0 to NoOfRows('Temp_Variables') - 1

  let vName = peek('Name', i, 'Temp_Variables'); // Name of the variable

  let vComment = peek('Comment', i, 'Temp_Variables'); // Comment/description of the variable

  let $(vName) = peek('Expression', i, 'Temp_Variables') & chr(10) & '/* $(vComment) */'; // Expression

next i

// Remove temp variables

let i = null();

let vName = null();

let vComment = null();

// Remove temp tables

drop table Temp_Variables;

3) View the variable in variable overview

4) Use vVar1 in your 3-4 different charts... the final usage will differ based on your requirement, but one of these two should work vVar1 or $(vVar1)

5) If you still don't want to use Excel, simply copy and paste your expressions in the variable overview (manually) and then use the variable... either ways your expression still looks like how you wanted it to look....

Best,

Sunny

p.s. attaching both Excel and QVW sample with this response

[cicciput]

Hello Sunny,

skipping all the excel file part, according to what you write, can I only work in variable overview?

Can't I do the same in the load editor? If so, how? (I tried with no luck when having expressions span multiline).

Thanks for your time!

P.S.: two of the advantages of the load editor are I can export and import a single script rather than copy and paste several variables once at a time and also I can compare them in place rather than skipping from one var to another.

[sunny_talwar · Accepted solution]

You can use them in script, but the use becomes challenging because of the dollar ($) sign. The dollar sign is usually expanded in the script and in order to overcome this problem there are two ways.

1) Use another character in place of $ sign and then have another variable replace the new character back to $. Look at jacob's response on Mar 21, 2012 at 10:54 am

Re: Stop Dollar Sign Expansion in the script (Escape Character ??? )‌

2) Break down your variable so that $ and () are separated into two pieces. Stefan's response which was also the correct answer in the below thread

Expression as a variable with $-sign expansion

[cicciput]

Thank you for the valuable insights!

Accepted answer!