0 Replies Latest reply: Mar 7, 2014 11:10 AM by Christof Schwarz RSS

    FORSEMO Generator (Formula & SetModifier Generator)

    Christof Schwarz

      This piece of script allows you to make complex set modifier and formula definitions inside of a simple, easily readable and maintainable INLINE or Excel table. You will be able to define filter sets in such a simple way, that each data owner from the business can maintain the definitions (and doesn't need to hold QV-certificate in Design/Developmen) because there won't be a need to know the set modifier syntax. Even set operations (+, -, *, / ) can be defined very easily in a simplified syntax.

      Giving you an example: Your formula to calculate the current previous-year's versus this month's sales as a percentage would be

       

      SET xSalesDiff = Num(Sum({<[Year]={'2013'}, [Month]={'Oct'}, [Store]={'Abel'}>} Sales)/Sum({<[Year]={'2012'}, [Month]={'Oct'}, [Store]={'Abel'}>} Sales) -1, '0,0%');


      Using FORSEMO the same definition will be generated automatically if you provide the following table:

       

      KPI Formula Format Year§1 Month§1 Store§1 Year§2 Month§2 Store§2
      xSalesDiffSum(§1 Sales)/Sum(§2 Sales) -10,00%2013OctAbel2012OctAbel

       

      You can even trigger more complex set operators in a readable syntax. e.g. If you put in column Year§1

      • -2013  .... this will turn into <Year -= {2013}>
      • +'2010','2011' -'2013'.... this will turn into <Year += {'2010', '2011'} - {'2013'}>


      Check out my 5"-Flash-video and you will understand, then get your FORESEMO masters degree by reading through my script inline remarks documentation.