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

    Using multiple listbox selectors in formulas

    Kyle Luckow

      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!