Using set analysis to get a value of previous period

[wandoqebu]

Hi. I have a dataset with the fields: Account_id, account_score, year_quarter, YearQuarterKey (which is a running sequencial number for every year-quarter combination). I want to use set analysis in a table presentation on my app, and to add the field previous_quarter_score to every row ! in the table, according the field YearQuarterKey

I tried:

=max( {< YearQuarterKey = {"$(=(YearQuarterKey-1))"} >} account_score)

It did not work.

Any thoughts on how to accomplish this?

Thanks.

[dmac1971]

Hi try this :

=sum( {< YearQuarterKey={"$(=max({$} YearQuarterKey)-1)"} >} account_score)

[marcus_sommer]

It's not possible in this way. You could use something like:

YearQuarterKey = {"$(=max(YearQuarterKey)-n)"}

to reference to any wanted period - but you mustn't have any of the period-fields within your object. There are various reasons for it, for example the needed $-sign expansion creates an adhoc-variable which is evaluated ones before the chart is calculated and this single result is then applied to all rows. Further such set analysis condition defines a certain selection state which didn't fits to the related dimension-value which would results in zero or NULL (there are ways to enforce to show any expression result to any dimension-value but it has so many disadvantages that it couldn't be recommended).

Quite common for such scenarios is not to apply the period-field as dimension else to apply n expressions - for each wanted period an own one. Usually it are quite a few one and even if it are 12 or more - per copy & paste and adjusting the n it's not difficult or needs much efforts.

Another approach would be to keep the period-field and using interrecord-functions like above() or before() to access the surrounding cells within the object which would you enable to set the current and previous results side by side and comparing them.