0 Replies Latest reply: Mar 4, 2016 11:01 AM by Kyle Luckow RSS

    Using multiple listbox selectors in formulas

    Kyle Luckow



      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



      //*******Listbox time selector******************



           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******************



      dual(Version, SortOrder) as VersionFilter2,

          SortOrder as VersionOrder2,


      INLINE [

      Version, SortOrder,  Version2

          Actual,  1,   ActualFormula2

          Plan,  2,   PlanFormula2


      //*******Listbox Formula Selector******************

      dual("Metric", SortOrder) as "Selector2",
          SortOrder as SMOrder2,
      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!