I have a project where I need 48 variations of an expression, so i'm trying to find a way to use variables as much as possible.
The chart will display average orders placed per person in 6 time increments (30/60/90 days/6 mth/12mth) before and after a point in time based on user selection via button that sets 4 variables (in blue below). The final expression looks something like this:
Count(DISTINCT {<OccurrenceFlagField = {'CriteriaPrefixTimeIncrement'}>} ORDER_ID) / Count(DISTINCT {<PersonFlagField = {PersonCriteria}>} PERSON_ID)
For each possible event (registration date, first online order date, first any coupon use date, first specific coupon use date), I have buttons that set variables corresponding to the text in blue above:
OccurrenceFlagField - a field on the orders table for each event that contains a value indicating whether the order falls into one of the 12 date ranges for the consumer (ie. 'RegDt-30~FirstOnl+6m' indicates the order was placed within 30 days before registration date and within 6 months after first online order was placed)
CriteriaPrefix - The string value in the flag field that corresponds to the selected event (i.e. 'RegDt' or 'FirstOnl')
PersonFlagField - the field which indicates the person qualifies to be counted in the expression (i.e. RegisteredFlg or OnlineOrderCount)
PersonCriteria - indication criteria for the set analysis (i.e. '1', '>0')
I have the above expression in a variable that concatenates all the text strings together with the 4 variables and it works, with TimeIncrement hard-coded. But for the sake of maintenance I'd rather not have 12 versions of the expression variable in my design (I WILL forget to update them all when changes are made). I tried to break up the expression into parts before and after the time increment, and just hard code the time increment in the expression on the chart, but it won't calculate that way, although when i hover over the expression header, the expression looks the same as it originally did when i had one variable with the time increment hard-coded.
VariableA = Count(DISTINCT {<OccurrenceFlagField = {'CriteriaPrefix
VariableB = '}>} ORDER_ID) / Count(DISTINCT {<PersonFlagField = {PersonCriteria}>} PERSON_ID)
Expression: =$(VariableA) & '-12m' & $(VariableB)
I'm working on a sample doc to better represent what I'm looking to do, and will attach it as soon as I can. Any ideas are appreciated.
