Partial or nested expressions

[alenb]

I'm not sure how to name this concept that I have in mind, but this is what I want to achieve.

I have my expressions stored as string variables, but maybe it can be done differently. 

Let exp_LastMonthINT = 'sum({$ < BillingMonth = {'10'}, BillingYear = {'2020'}, DistChannel = {INT} > } Units)';

Let exp_LastMonthWHLS = 'sum({$ < BillingMonth = {'10'}, BillingYear = {'2020')}, DistChannel = {WHLS} > } Units)';

Instead of repeating the whole parts of expression, I would rather do it in this sort of way:

1. Define this partial expression 

Let exp_LastMonth = 'sum({$ < BillingMonth = {'10'}, BillingYear = {'2020'}>})';

2. Use it in other expressions (I'm making up the syntax)

Let exp_LastMonthINT = (DistChannel = {INT}  exp_LastMonth)

Let exp_LastMonthWHLS = (DistChannel = {WHLS}  exp_LastMonth)

Is something like this possible?

[rwunderlich · Accepted solution]

Yes, possible. You have to make sure when the variables are substituted the final syntax is correct. 

Reference another variable using $().  Note typically you want to use SET instead of LET. So from your example:

Set exp_LastMonthINT = (DistChannel = {INT}  $(exp_LastMonth))

The resulting expression would be invalid:

(DistChannel = {INT} sum({$ < BillingMonth = {'10'}, BillingYear = {'2020'}>})

You could do something like this to get a valid expression for exp_LastMonthINT

Set set_LastMonth = BillingMonth = {'10'}, BillingYear = {'2020'};
Set exp_LastMonthINT = sum({$ <$(set_LastMonth), DistChannel = {INT} > } Units);

In a chart measure you would then use:

$(exp_LastMonthINT)

-Rob

[QFabian]

hi, yeah, totally possible and common use, a few opctions now :

1.- try wothout making them text.

2.- create a inline table with your entire desired expressions like this :

LOAD * INLINE [
Field1, Field2
Formula1, "sum({$ < BillingMonth = {'10'}, BillingYear = {'2020'}>}DistChannel = {INT} exp_LastMonth)"
Formula2, "sum({$ < BillingMonth = {'10'}, BillingYear = {'2020'}>}DistChannel = {WHLS} exp_LastMonth)"
];

next, asign this to a variable :

vFormula =F2

and then use it in your expresion like this :

=$(vFormula)

then, in the sheet, add the filter to select the formula!