FirstSortedValue and Aggr

[timpoismans]

Hi,

I've been through a few posts already, but I can't seem to figure it out.

I got the following table (which is a visual representation of my aggr measure):

%WRFINX                   NACPRD                  Measure1

1530                             2017-11                    0

1530                             2017-12                    1546

1530                             2018-01                    1546

1530                             2018-02                    -1952

1530                             2018-03                    -1952

Measure1 contains the following measure;

Aggr(Sum({<PAGEID={'BudgettaireControleDethier'},CCOL1={'TOTAAL'}>}NCOL3),NACPRDNR,[%WRFINX])

Now I want to have the value returned from Measure1, for the last period(NACPRD). But by using the following:

FirstSortedValue(Aggr(Sum({<PAGEID={'BudgettaireControleDethier'},CCOL1={'TOTAAL'}>}NCOL3),NACPRDNR,[%WRFINX]),-NACPRDNR)

it returns Null().

I'm at a loss of how to retrieve the value from the last NACPRD and would appreciate every help.

Thanks in advance!

Kind regards,

Tim P.                   

[ogautier62]

Hi,

I don't think you need firstsortedValue (look at the doc for use, parameters aren't good)

for the last expression use bottom(measure1)

regards

[sunny_talwar]

How about this

FirstSortedValue(Aggr(Sum({<PAGEID = {'BudgettaireControleDethier'}, CCOL1 = {'TOTAAL'}>} NCOL3), NACPRDNR,[%WRFINX]), -Aggr(NACPRDNR, NACPRDNR, [%WRFINX]))

[timpoismans]

Already tried it like that, but returns Null values.

Could it have anything to do with the format of the NACPRDNR?

[sunny_talwar · Accepted solution]

Is NACPRDNR read as a date? if not, then try this

FirstSortedValue(Aggr(Sum({<PAGEID = {'BudgettaireControleDethier'}, CCOL1 = {'TOTAAL'}>} NCOL3), NACPRDNR,[%WRFINX]), -Aggr(Date#(NACPRDNR, 'YYYY-MM'), NACPRDNR, [%WRFINX]))

[timpoismans]

Bottom only works if I keep the NACPRDNR in my table. Which isn't the purpose.

I want to see the %WRFINX and then the value based on Measure1.

My apologies if I wasn't clear about that.

[timpoismans]

Yup, that was the issue. NACPRDNR was just seen as text and not as Date. Adding the dateformat fixed it and now it does return the correct value!

Thank you very much again, Sunny!