Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
timpoismans
Specialist
Specialist

FirstSortedValue and Aggr

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.                   

1 Solution

Accepted Solutions
sunny_talwar

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]))

View solution in original post

6 Replies
ogautier62
Specialist II
Specialist II

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
Specialist
Specialist
Author

Already tried it like that, but returns Null values.

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

sunny_talwar

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
Specialist
Specialist
Author

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
Specialist
Specialist
Author

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!