Hello,
I'm trying to set up a sheet that allows a user to select a time period and the comparison to be either to last year or to plan through the use of the listbox extension. I've had success with using this technique when I allow the user to select the time period and a specific metric but in the case of plans, the formula completely changes. See an example below
Script:
//*******Listbox time selector******************
TimeFilter:
LOAD
dual(ToDate, SortOrder) as TimeFilter,
dual(AbbrToDate, SortOrder) as AbbrevTimeFilter,
CurrSet, // for dynamic expressions
PrevSet // for dynamic expressions
INLINE [
ToDate, SortOrder, AbbrToDate, CurrSet, PrevSet
YearToDate, 1, YTD, "CurrentYearToDate={1},", "PriorYearToDate={1},"
MonthToDate, 2, MTD, "CurrentMonthToDate={1},", "PriorMonthToDate={1},"
PeriodToDate, 3, PTD, "CurrentPeriodToDate={1},", "PriorPeriodToDate={1},"
];
//*******Listbox Plan or Actual Selector******************
VersionSelector2:
Load
dual(Version, SortOrder) as VersionFilter2,
SortOrder as VersionOrder2,
Version2
INLINE [
Version, SortOrder, Version2
Actual, 1, ActualFormula2
Plan, 2, PlanFormula2
];
//*******Listbox Formula Selector******************
Selector2:
LOAD
dual("Metric", SortOrder) as "Selector2",
SortOrder as SMOrder2,
ActualFormula2,
PlanFormula2
INLINE [
"Metric", SortOrder, ActualFormula2, PlanFormula2,
"Units Booked", 1, "num(SUM({<EventType={'Order'},IsOrderCancelled={0}>} TotalSoldDoorCount) + SUM({<EventType={'Order'},IsOrderCancelled={0}>} TotalSoldWindowCount),'#,##0')", "num((SUM({<EventType={'Plan'}>} PlanDoorUnits) + SUM({<EventType={'Plan'}>} PlanWindowUnits)),'#,##0')"
"Net Booked $s", 2, "money(SUM({<EventType={'Order'},IsOrderCancelled={0}>} SalePrice)/1000, '$#,##0k;($#,##0k)')", "money(SUM({<EventType={'Plan'}>} "PlanNetOrders$")/1000, '$#,##0k;($#,##0k)')"
];
Original Formula that allows the user to change TimeFilter:
$(=replace(only({1<SMOrder2={1}>} ActualFormula2), 'EventType', CurrSet & 'EventType'))
Original Formula that allows the user to change TimeFilter and Selector 2 (Metric for only ActualFormula2):
$(=replace(only({1<SMOrder2={"$(=min(SMOrder2))"}>} ActualFormula2), 'EventType', CurrSet & 'EventType'))
Goal is to create a formula that allows the user to change TimeFilter and Selector2 and new VersionFilter2
I was thinking a replacement could be included somehow that would have the VersionFilter2 select if it should grab the ActualFormula2 or the PlanFormula2 while still keeping the TimeFilter and Metric selector working. Any ideas?
As always, your help is greatly appreciated!
