Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

timpoismans
Contributor III

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.                   

Tags (2)
1 Solution

Accepted Solutions
MVP
MVP

Re: FirstSortedValue and Aggr

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

6 Replies
ogautier62
Valued Contributor II

Re: FirstSortedValue and Aggr

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

MVP
MVP

Re: FirstSortedValue and Aggr

How about this

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

timpoismans
Contributor III

Re: FirstSortedValue and Aggr

Already tried it like that, but returns Null values.

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

MVP
MVP

Re: FirstSortedValue and Aggr

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
Contributor III

Re: FirstSortedValue and Aggr

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
Contributor III

Re: FirstSortedValue and Aggr

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!