17 Replies Latest reply: Sep 29, 2017 9:07 AM by Sunny Talwar

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

• ###### Re: Sum 8 bottom values of expression, multidimensional

Hi,

can you share a portion of you data ?

• ###### Re: Sum 8 bottom values of expression, multidimensional
 Resource Date Xpression A 9/1/2017 55129970.53 A 9/2/2017 57167731.16 A 9/3/2017 59924792.42 A 9/4/2017 56890480.32 A 9/5/2017 50053993.14 A 9/6/2017 55682676.22 A 9/7/2017 60126713.4 A 9/8/2017 53195220.8 A 9/9/2017 56569199.48 A 9/10/2017 72184943.68 A 9/11/2017 60804672.53 A 9/12/2017 56743948.01 A 9/13/2017 56781501.64 A 9/14/2017 59390731.71 A 9/15/2017 55643798.58 A 9/16/2017 66557110.81 A 9/17/2017 58809720.64 A 9/18/2017 50797050 A 9/19/2017 47482589.05 A 9/20/2017 55293088.45 A 9/21/2017 38390242.05 A 9/22/2017 46660739.17 A 9/23/2017 58357438.72 A 9/24/2017 56883278.86 A 9/25/2017 40359446.39

Time is used for Xpression calculation

• ###### 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)

• ###### 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

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

Both return 0s Sunny

• ###### Re: Sum 8 bottom values of expression, multidimensional

or maybe this:

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

• ###### Re: Sum 8 bottom values of expression, multidimensional

Thank you for the responses.

@kushal and Frank: it returns 0s

 Resource Name Date Xpression rank(Xpression) if(aggr(rank(-Sum(Xpression)),Resource)<=8,Xpression) =sum({} Xpression) A Totals 72184943.68 - 72184943.68 0 A 9/1/2017 55129970.53 18 - 0 A 9/2/2017 57167731.16 9 - 0 A 9/3/2017 59924792.42 5 - 0 A 9/4/2017 56890480.32 10 - 0 A 9/5/2017 50053993.14 21 - 0 A 9/6/2017 55682676.22 15 - 0 A 9/7/2017 60126713.4 4 - 0 A 9/8/2017 53195220.8 19 - 0 A 9/9/2017 56569199.48 14 - 0 A 9/10/2017 72184943.68 1 - 0 A 9/11/2017 60804672.53 3 - 0 A 9/12/2017 56743948.01 13 - 0 A 9/13/2017 56781501.64 12 - 0 A 9/14/2017 59390731.71 6 - 0 A 9/15/2017 55643798.58 16 - 0 A 9/16/2017 66557110.81 2 - 0 A 9/17/2017 58809720.64 7 - 0 A 9/18/2017 50797050 20 - 0 A 9/19/2017 47482589.05 23 - 0 A 9/20/2017 55293088.45 17 - 0 A 9/21/2017 38390242.05 26 38390242.05 0 A 9/22/2017 46660739.17 24 - 0 A 9/23/2017 58357438.72 8 - 0 A 9/24/2017 56883278.86 11 - 0 A 9/25/2017 40359446.39 25 - 0

If is not working as expected.

• ###### Re: Sum 8 bottom values of expression, multidimensional

have you tried with below expression

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

• ###### 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.

• ###### Re: Sum 8 bottom values of expression, multidimensional

May be 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))

Updated the expression as I missed one of the closing parenthesis for the Rank function before

• ###### Re: Sum 8 bottom values of expression, multidimensional

It throws error in expression. Trying to fix it and let you know. Thx

• ###### 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))

• ###### Re: Sum 8 bottom values of expression, multidimensional

Wow, it is working. Need a couple of days to understand it

• ###### Re: Sum 8 bottom values of expression, multidimensional

I just did this

Sum(Aggr(If(Rank(-YourExpression) < 9, YourExpression, Resource, Date))

• ###### Re: Sum 8 bottom values of expression, multidimensional

cha gaye guru

• ###### Re: Sum 8 bottom values of expression, multidimensional

Sab aapse hee sikha hai sirji...