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