Filtering dimension on expression results

Thu, 31 May 2012 07:55:49 GMT

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

topic Filtering dimension on expression results in QlikView

Filtering dimension on expression results