Qlik Community

QlikView Expressor

Discussion Board for collaboration related to QlikView Expressor.

nikolis_gr
New Contributor II

Sum 8 bottom values of expression, multidimensional

Hello all,

I am loading data on monthly basis and I have created a report with dimensions Resource, Date, Time. What I am trying to do is to report per Resource the sum of the 8 worst values of an expression that is calculated daily (let's call it Xpression).

I am trying to calculate the sum of the 8 bottom values of Xpression monthly for every Resource.

I have tried this (which will not do what I want since it will get the top 8 values) with no luck at all, it returns 0 values:

=sum({<Resource= {"=rank(sum(Xpression),4,0) <=8"},Date>} Xpression)

1 Solution

Accepted Solutions
MVP
MVP

Re: Sum 8 bottom values of expression, multidimensional

Hahaha, I just fixed a parenthesis error, try this

Sum(Aggr(If(Rank(

-Max(TOTAL <Resource, Date> Aggr(

If(Max(TOTAL <Resource, Date> Aggr(RangeSum(Above([MAX_RATE],0,4)), Resource, Date, ([Time], (Ascending)))) = RangeSum(Above([MAX_RATE],0,4)), RangeMax(Above([MAX_RATE],0,4)))

, Resource, Date, ([Time], (Ascending))))) < 9,

Max(TOTAL <Resource, Date> Aggr(

If(Max(TOTAL <Resource, Date> Aggr(RangeSum(Above([MAX_RATE],0,4)), Resource, Date, ([Time], (Ascending)))) = RangeSum(Above([MAX_RATE],0,4)), RangeMax(Above([MAX_RATE],0,4)))

, Resource, Date, ([Time], (Ascending)))))

, Resource, Date))

17 Replies
YoussefBelloum
Esteemed Contributor

Re: Sum 8 bottom values of expression, multidimensional

Hi,

can you share a portion of you data ?

nikolis_gr
New Contributor II

Re: Sum 8 bottom values of expression, multidimensional

ResourceDateXpression
A9/1/201755129970.53
A9/2/201757167731.16
A9/3/201759924792.42
A9/4/201756890480.32
A9/5/201750053993.14
A9/6/201755682676.22
A9/7/201760126713.4
A9/8/201753195220.8
A9/9/201756569199.48
A9/10/201772184943.68
A9/11/201760804672.53
A9/12/201756743948.01
A9/13/201756781501.64
A9/14/201759390731.71
A9/15/201755643798.58
A9/16/201766557110.81
A9/17/201758809720.64
A9/18/201750797050
A9/19/201747482589.05
A9/20/201755293088.45
A9/21/201738390242.05
A9/22/201746660739.17
A9/23/201758357438.72
A9/24/201756883278.86
A9/25/201740359446.39

Time is used for Xpression calculation

prat1507
Valued Contributor

Re: Sum 8 bottom values of expression, multidimensional

Maybe this,.

=Sum(if(aggr(rank(-Sum(Xpression)),Resource)>=1

and aggr(rank(-Sum(Xpression)),Resource)<=8,Xpression)

Re: Sum 8 bottom values of expression, multidimensional

just use minus '-' in your expression

sum({<Resource= {"=rank(- sum(Xpression),4,0) <=8"},Date>} A)

Frank_Hartmann
Honored Contributor

Re: Sum 8 bottom values of expression, multidimensional

or maybe this:

=sum({<Xpression= {"=rank(-sum(Xpression),4,0) <=8"},Date>} Xpression)

nikolis_gr
New Contributor II

Re: Sum 8 bottom values of expression, multidimensional

Thank you for the responses.

@kushal and Frank: it returns 0s

Please check below:

      

Resource NameDateXpressionrank(Xpression)if(aggr(rank(-Sum(Xpression)),Resource)<=8,Xpression)=sum({<Xpression= {"=rank(aggr(-sum(Xpression),Date),4,0) <=8"},Date>} Xpression)
ATotals72184943.68-72184943.680
A9/1/201755129970.5318-0
A9/2/201757167731.169-0
A9/3/201759924792.425-0
A9/4/201756890480.3210-0
A9/5/201750053993.1421-0
A9/6/201755682676.2215-0
A9/7/201760126713.44-0
A9/8/201753195220.819-0
A9/9/201756569199.4814-0
A9/10/201772184943.681-0
A9/11/201760804672.533-0
A9/12/201756743948.0113-0
A9/13/201756781501.6412-0
A9/14/201759390731.716-0
A9/15/201755643798.5816-0
A9/16/201766557110.812-0
A9/17/201758809720.647-0
A9/18/20175079705020-0
A9/19/201747482589.0523-0
A9/20/201755293088.4517-0
A9/21/201738390242.052638390242.050
A9/22/201746660739.1724-0
A9/23/201758357438.728-0
A9/24/201756883278.8611-0
A9/25/201740359446.3925-0

If is not working as expected.

MVP
MVP

Re: Sum 8 bottom values of expression, multidimensional

From what I am seeing the problem is that the rank is not needed at the Resource level, but Resource and Date level... so either create a new field in the script like this

LOAD Resource,

     Date,

     Resource&Date as Key,

     ....

FROM...

and then this

Sum({<Key= {"=rank(-Sum(Xpression),4,0) <=8"},Date>} A)

Or without changes to the script... try this

Sum(Aggr(If(Rank(-Sum(Xpression),4,0) <= 8, A), Resource, Date))

Re: Sum 8 bottom values of expression, multidimensional

have you tried with below expression

sum({<Resource= {"=rank(- sum(Xpression),4,0) <=8"},Date>} Xpression)

nikolis_gr
New Contributor II

Re: Sum 8 bottom values of expression, multidimensional

It still returns 0 values.

Maybe I should explain the Xpression:

Max(TOTAL <Resource, Date> Aggr(

If(Max(TOTAL <Resource, Date> Aggr(RangeSum(Above([MAX_RATE],0,4)), Resource, Date, ([Time], (Ascending)))) = RangeSum(Above([MAX_RATE],0,4)), RangeMax(Above([MAX_RATE],0,4)))

, Resource, Date, ([Time], (Ascending))))

The measure is MAX_RATE. I am calculating a rolling sum of 4 MAX_RATE in a row, I am calculating the max of these sums and return the top value of the 4 values that build it.

Now I would like to get the bottom eight values of the above Xpression per month.

Community Browser