Trying to reduce the complexity of my formulas, by converting part of the formulas into variables.
My current formulas look something like this:
if ( variable= Annual , big_formula_v1 , if ( variable = Quarterly, big_formula_v2 , big_formula_v3))
if evaluates whether a user drop-down value equals Annual, Quarterly (or Monthly if neither of the previous)
formulas 1,2,3 are the same as each other, except they use either CalYear, CalYearQuarter or CalYearMonth field to evaluate the result, depending which condition is met
Would like to standardise the above, by creating one standard formula, which specifies the correct fields to use (CalYear, CalYearQuarter etc..)
Am unsure how best to proceed, ie do I need to create the variables in the script, using the "LET" statement, whilst still using a dropdown list to define the time period? Can all this be done in the "Variable Overview"?
Would like to create something similar to the following:
if ( variable = Annual, TimePeriod = CalYear ,
if ( variable = Quarterly, TimePeriod = CalYearQuarter ,
TimePeriod = CalYearMonth ))
where I am defining a variable (TimePeriod), which returns the correct field to evaluate:
TimePeriod = either CalYear, CalYearQuarter or CalYearMonth
Variables and Set Analysis are never the best of bed fellows - usually due to the inclusion / exclusion of the single quotations.
In the attached I've brought the time periods in using an inline load - both the field name (CalYear, CalYearMonth CalYearQuarter) and the 'Mapping' to be presented to the user (Year, Quarter, Month) instead of using a calculated variable.
I've set a new variable that simply looks at the CalYear/Month/Quarter value which is selected by the user selecting the Year/Quarter/Month thanks to its association.
This allows us to pass the true field value into the expression without the single quotes that came from your TimePeriod expression but crucially allows the end user to select the field based on a mapping (via association).
It seems to work for me returning the minstring() value as required.
variables can be created either in the load script, using statements SET or LET, or in the Variable Overview. Both ways are almost identical, except that variables created in the script are easier to re-create if you should lose them for some reason.
In your case, however, I'd rather recommend a data-driven solution, instead of variables and complex Set Analysis conditions...
We often create calendar flags - YTDFlag, MTDFlag, etc... In your case, instead of creating a "static" flag, you want to create another table with "Dynamic" flags, that can be "Annual", "Quarterly" or "Monthly". So, your new table could look like this:
Date,
TimeSelection, // "Annual", "Quarterly" or "Monthly"
DateFlag // =1 if Date belongs to the specified Time Selection, =null() if it doesn't
Once you create your table this way, and link it to the Date field, you can have a simple expression like this:
sum( { < DateFlag = {1} >} Euros)
The selection of the field "TimeSelection" will drive the relevant date periods to be selected.
Variables and Set Analysis are never the best of bed fellows - usually due to the inclusion / exclusion of the single quotations.
In the attached I've brought the time periods in using an inline load - both the field name (CalYear, CalYearMonth CalYearQuarter) and the 'Mapping' to be presented to the user (Year, Quarter, Month) instead of using a calculated variable.
I've set a new variable that simply looks at the CalYear/Month/Quarter value which is selected by the user selecting the Year/Quarter/Month thanks to its association.
This allows us to pass the true field value into the expression without the single quotes that came from your TimePeriod expression but crucially allows the end user to select the field based on a mapping (via association).
It seems to work for me returning the minstring() value as required.
