FORSEMO Generator (Formula & SetModifier Generator)

ChristofSchwarz — Tue, 29 Oct 2013 08:52:50 GMT

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
xSalesDiff	Sum(§1 Sales)/Sum(§2 Sales) -1	0,00%	2013	Oct	Abel	2012	Oct	Abel

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.

topic FORSEMO Generator (Formula & SetModifier Generator) in QlikView

FORSEMO Generator (Formula & SetModifier Generator)