Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.