0 Replies Latest reply: May 31, 2012 3:55 AM by Alexis VAILLANT RSS

    Filtering dimension on expression results

    Alexis VAILLANT

      Hi all,

       

      I would like to provide my users with the capabilty to filter a table (graph as a table) on expression results then to export it in excel.

      The filter will be entered through Input Boxes

       

      For the table creation: no issue

      For the expressions: no issue

      For the export: no issue

      For the dimension filtering:

       

      I have different expression such as:

      "Historical Average"

      =SUM({<$(vExpedie)>} item_qte*item_prix)/Count({<$(vExpedie)>} Distinct([ID de la commande])) => this give the overall order average for order shipped

      "Last 6 months average"

      =SUM({<$(vExpedie), $(vLast6Months)>} item_qte*item_prix)/Count({<$(vExpedie),$(vLast6Months)>} Distinct([ID de la commande])) // this give the same for the last 6 months

       

      where:

      vExpedie,vLast 6 months are setup in script as:

      LET vExpedie= '[Etat de la commande] = {' & chr(39) & 'expediée' & chr(39) & '}';

      LET vLast6Months = '[MaDate] = {">=' & chr(36) & '(=DATE(MONTHSTART(ADDMONTHS(DATE_REFERENCE,-6))))<=' & chr(36) & '(=DATE(MONTHSTART(DATE_REFERENCE)-1))"}';

       

      What i would like to do is to be able to filter the results on

      Input_box_1<Historical Average<input_box_2

      input_box_3<Last 6 Months Average<input_box_4

       

      My first thought was to use calculated dimension but the set analysis seems not to be usable to calculate a dimension, so it become a nightmare to use aggr to filter the dimension.

       

      Any idea to make it simple?

      Thanks in advance