Use loose table field as filter in setanalysis

[p_noindl]

Hi all,

I would like to enable the user to select different periods (months, year, mat, yrd,...) and to show all three expressions (for actual period, previous period and same period last year - all related periods are set manually in a database table). It is an easy job with an if expression, but the user has millions of data rows and that makes it really slow. I cannot work with flags, calendars, e.g. because of the unlogical period definitions - in the attached sample app I have done it with: Sum(if(TimeDimension=Time, Sales)) for Actual, Sum(if(TimeDimension=TimeVP, Sales)) for Previous,..  

Before, after and p() don't work, because the periods are not selected one after the other.

Anybody knows an equivalent to the if-expression in set analysis?

Many thanks in advance for your assistance, a sample app is attached.

Best regards Peter

[marcus_sommer · Accepted solution]

I'm not sure but I think that this is rather not possible at least not with the given data-structure. But by considering any changes within the datamodel I could imagine that a "complete" As-Of-Table might be easier as any set analysis approach: The As-Of Table.

This means extending the TimeDim table to a main-period and sub-period category. The already existing TimeDimension is then the main category and Actual, Previous and LastYear are then the sub-category values. It's quite similar to the existing approach just duplicating it thrice (your link-table would have than approximately 20k records which shouldn't cause serious performance issues).

With this you could use both fields as horizontal dimensions within the table and as expression a simple sum(Sales) should work.

- Marcus

[p_noindl]

Hi Marcus,

many thanks for the hint. I could solve it now within the script in a similar way like the 'AsOfTable' concept.

Best regards

Peter