Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
kluckow0128
Not applicable

Using multiple listbox selectors in formulas

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!