3 Replies Latest reply: Jan 6, 2016 2:34 AM by Anitha Mohanraj RSS

    Dynamically restrict Dimension Value

    Anitha Mohanraj

      Hi All,

      We have a dashboard in which user is allowed dynamically to select Month OR Week as the time dimension.

      Since the underlying fact table has both week and month, there will be some records where a week falls under 2 months.

      The table also has week offset and month offset identifiers.

      Month Offset = 0 identifies the current month. Although we take care to load only till last completed week, there will be data for the current month. Since in most cases, the current month data will be incomplete, the requirement is to avoid the display of the current month data, when Month view is chosen.


      Here are the details of the implementation so far:

      [Time Dimension]:

      LOAD * INLINE [

          TID, Time

          1, Week

          2, Month

      ];

       

      There is this array called Time dimension. The values in the "Time" column viz, Week and Month are available in my fact table.

       

      I also have

      1) a List box based on this Time column. User can choose Week or Month using this list box. Also, this List box has been set up like a Radio Button (at least and only one column can be selected).

      2) 2 sliders: One for week and another for month. The sliders overlap each other. In the Layout tab of both the sliders, I have set the "Conditional" as "=SubStringCount('|' & Concat(distinct Time, '|') & '|', '|Month|')" for Month Slider AND "=SubStringCount('|' & Concat(distinct Time, '|') & '|', '|Week|') for the Week Slider.

      3) a chart that has [$(=[Time])] as the Time dimension. The chart has a few other dimensions and expressions as well. This is a line chart.

       

      Given this setup, how can i restrict the display of the current month (month Offset =0) in both the Month Slider and in the chart, only when the Month option is selected in the List Box? Attached is a sample qvx file.

       

      Please let me know.

       

      Thanks.