Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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?
Create a year field in the script, and then the following should work.
FirstSortedValue(DISTINCT{<date,Category={'cat_1'}, year={"<$(=max(year))"}>}position, -date)
Hi, can you post a link to the previous question to check? and what's the difference between this one and the last one?
Hi @rubenmarin ,
here is the link:
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).
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.
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.
Create a year field in the script, and then the following should work.
FirstSortedValue(DISTINCT{<date,Category={'cat_1'}, year={"<$(=max(year))"}>}position, -date)