topic Re: Help with using Rank / Aggr in QlikView

Help with using Rank / Aggr

Anonymous — Mon, 16 May 2016 12:46:15 GMT

Hello everyone,

I have a list box with an expression that is supposed to show a mini trendline covering data from the last 4 weeks (below).

Unfortunately, the expression I am using to extract the values for the last 4 weeks is not working.... The underlying data is shown in the table on the left, however my expression:

=Aggr(If(Rank(DashboardDate) <=4, [Total Logged Last Week]) ,[Total Logged Last Week])

appears to be bringing back the data shown on the right.

I would like to bring back only the top 4 dates (sorted descending) and their corresponding values - can someone help me see where I am going wrong?

Thanks in advance!

Re: Help with using Rank / Aggr

swuehl — Mon, 16 May 2016 16:07:25 GMT

Try a dimension DashboardDate and as expression

=Sum({<DashboardDate = {">=$(=Date(Max(DashboardDate,4),'DD/MM/YYYY'))"} >} [Total Logged Last Week])

Re: Help with using Rank / Aggr

ramoncova06 — Mon, 16 May 2016 16:41:54 GMT

you can use an advanced search to get the top 4 weeks

sum({< DashboardDate ={"=rank(Sum(Total Logged Last Week])) <= 4 "}>}[Total Logged Last Week])

Re: Help with using Rank / Aggr

Anonymous — Tue, 17 May 2016 10:42:50 GMT

This didn't work unfortunately 😞

Re: Help with using Rank / Aggr

Anonymous — Tue, 17 May 2016 10:43:00 GMT

Perfect!! Thanks!!