Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
JMAROUF
Creator II
Creator II

Max value of the last year

Hi everyone,

i have a star schema with a multikpi visualisation and i want to get the max position (rank_invest)  of the max date of the previous Year (rank_invest)  of the category 1 (investor) for each invested (dimension) , here is an example of the model:

JMAROUF_2-1633683090847.png

 

 

the output desired:  if the user selects any date of 2021 then:

- invested_10 : 1,5  (previous year 2020,  max position 1,5 , cat_1)

- invested_20 : 1,8 (previous year 2020, max position 1,8 , cat_1)

.... 

 i have avoided the set analysis because each invested could have a specific max date of the last year as @rubenmarin  has mentioned in a previous answer, but the problem with if() is that i can't choose a non selected date, when the user selects N, i can't use (non selected ) values of N-1.

 

Can anyone help please?

Labels (1)
1 Solution

Accepted Solutions
tresesco
MVP
MVP

Create a year field in the script, and then the following should work.

FirstSortedValue(DISTINCT{<date,Category={'cat_1'}, year={"<$(=max(year))"}>}position, -date)

tresesco_0-1633947973149.png

 

View solution in original post

5 Replies
rubenmarin

Hi, can you post a link to the previous question to check? and what's the difference between this one and the last one?

JMAROUF
Creator II
Creator II
Author

Hi @rubenmarin ,

here is the link:

https://community.qlik.com/t5/New-to-Qlik-Sense/Max-value-of-the-max-previous-date/m-p/1840834/threa...

the difference now is that i want the max value of the last Year, if the user chooses N, ithe result should be max Date of N-1, and as i said above, i couldn't use if here because the values aren't in the selection (N-1).

rubenmarin

Hi, hard to guess without a sample to make some test, maybe:

Max(TOTAL {<date={"*/$(=Year(Max(date)-1))"}>} Aggr(If(date=Max(TOTAL <id_invested> date,2), Max(position)),id_invested,date))

Maybe it need more TOTAL clase to ignore dimensions (if the table where it is ahs dimensions, and set analysis with date to ignore selctions and make it work with last year dates.

JMAROUF
Creator II
Creator II
Author

Hi @rubenmarin ,

here is some data:

Investor:
Load * Inline
[
Category,investor,Id_investor
cat_1,investor_100,100
cat_2,investor_200,200
cat_1,investor_300,300
cat_3,investor_400,400
];

Rank_Invest:
Load * Inline
[
Id_investor,position,date,Id_invested
100,1.1,27/09/2021,10
100,1.2,25/09/2021,10
300,1.3,25/09/2021,10
100,1.4,20/09/2021,10
100,1.8,26/09/2021,20
300,1.6,25/09/2021,20
100,1.8,25/09/2021,20
400,1.9,25/09/2021,20
100,1.5,31/12/2020,10
300,1.9,28/12/2020,10
100,1.9,28/12/2020,10
400,2,31/12/2020,20
300,1.8,28/12/2020,20
100,1.5,28/12/2020,20

](delimiter is ',');

Invested:
Load * Inline
[
Id_invested,invested
10,invested_10
20,invested_20
30,invested_30
40,invested_40
];

 

can you explain please why did you put  "*/" in {<date={"*/$(=Year(Max(date)-1))"}, thank you in advance.

tresesco
MVP
MVP

Create a year field in the script, and then the following should work.

FirstSortedValue(DISTINCT{<date,Category={'cat_1'}, year={"<$(=max(year))"}>}position, -date)

tresesco_0-1633947973149.png