Using set analysis to get a value of previous period

[rujur]

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

[Vegar]

I can give you my best guess.

I'm guessing that you are making selection to field year_quarter. As your set modifier is on YearQuarterKey you are trying to combine a selection on this month in one field with an "extra" selection on YearQuarterKey in the expression. There is no intersect betweeen the two.

Try canceling our your year_quarter selection in your prev quarter expression. 

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

Hope this can be of help leading you to solve your issue.

-Vegar

[DorianS]

Hi, I think so. To retrieve the previous quarter's score using set analysis in Qlik Sense, you can use the aggr function in combination with max function. In qlik code the correct expression be the next (little changes):
=max(aggr( {< YearQuarterKey = {"$(=YearQuarterKey-1)"} >} account_score, Account_id, year_quarter))

This expression will calculate the maximum account_score for the previous quarter based on the YearQuarterKey, and it groups the calculation by Account_id and year_quarter to ensure you get the correct result for each row in your table presentation.

[cotiso_hanganu]

@DorianS 
Dorian,

To my humble knowledge, you need an aggregation function called inside the aggr(), orelse, what is the point of the aggr() ?

Have you ever used successfully an aggr without sum/max/only  inside !?

(if you have a working example, please share, it would be value for me , as well)

Sempre fi,
C

[cotiso_hanganu]

@rujur 

Set analysis is evaluated only once for the whole instance of the object (not like an if statement, that,  if it is called  inside a sum() expression, is reevaluated for each row of data.)

This is why I would add an AsOf script to get a working solution for what you need.
(there is a free SUB I recommend, already written by Rob Wunderlich that I've used for 10 years now. More details below ). Or you can write your own....

You need to create an additional table, that has min 3 columns: 

• YearQuarterKey,
• AsOfYearQuarterKey,
• YearQuarter(Keys)Ago. 
  (Preferable also AsOfYearQuarter)

Then in the Set analysis you will have
max( {<YearQuarter(Keys)Ago={'-1'}>}  account_score)
to evaluate the value associated with 1 quarter prior to the one specified in the dimension

Important: the dimension in the object should be  AsOfYearQuarter (and NOT YearQuarter !)

Once you understand the logic, is amazing how powerful and efficient  is this AsOf !

Also for more tedious tasks, as opening and closing balances or inventory...

AsOf Sub is included in QVC (Qlik View Components ) package of SUB scripts offered offered for free (!!!) by Rob over here https://qlikviewcookbook.com/ 

Lots of other valuable stuff there, as well : CookBook, QVC, Doc Analyzer,ScriptAnalyzer,...

Sempre fi,
C

PS: Please do not feel bad for not knowing about this ASOF... 
I am Qlik-in since 2000... and saw it in  Rob's collection since 2010, then in 2012 again, didn't understood the value, only in 2013 realized the gold inside. 

Rob, thank you one more time for all the value you have created for Qlik eco-system !

[blaise]

The most simple solution, from my POV would be to just use above(sum(account_score),1)  as expression and year_quarter as dimension (sort order asc). Replace above with below if you rather have desc sorting.

For get the simple solution to work with selections you need to add some set analysis like:

above(sum( {<year_quarter =, YearQuarterKey = {">=$(=min(YearQuarterKey)-1) <=$(=max(YearQuarterKey))"}>} account_score),1)

If you can provide some more context to the object where the value should be displayed I can help you write a correct expression if the above does not work. With more context I mean something like "a table with dimension x,y,z and expressions a,b,c.

You can use aggr for this but with the context provided I can't see any reasons to use it. And as other has pointed out, an aggr() without any inner expression is for most of the time useless, if you don't have any inner expression Qlik WILL use only(). I seldom use aggr() without both an inner and outer expression.

For the aggr lovers out there I recommend using the following to solve the requirement:

sum(aggr(rangesum(above(sum( {<year_quarter = >} account_score),1,1)),year_quarter))

When i was about to press the reply button I also saw that you have posted in the wrong channel - this channel is for application automation (QAA). You should get more replies if you post in a data analytics channel like https://community.qlik.com/t5/App-Development/bd-p/qlik-sense-app-development

[cotiso_hanganu]

It is a good point, as a solution without scripting !

 But be aware of:
- this solution can be less efficient for large data sets

- this solution can create additional issues when a pivot, or a circular field,  or a multiple dimensional columns table is used, when above might not get you back exactly what it was intended

=> Choose wisely !