Announcements
cancel
Showing results for
Did you mean:
Contributor

## Using set analysis to get a value of previous period

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.

Labels (1)
• ### General Question

2 Replies
Creator III

Hi try this :

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

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.

Tags
Community Browser