highlet highest and lowest values based on selection

[#1]

Hi

I have scenario

I have data like below

Date             Location      Sales        Growth

2016-01-01      Delhi          10000       -10000

2016-02-01      Delhi          15000         50%

2016-03-01      Delhi          20000          33% 

2016-04-01      Delhi          10000          -50%

2016-05-01      Delhi          30000           200%

2016-06-01      Delhi          45000           50%

2016-07-01      Delhi          60000           33%

2016-08-01      Delhi          50000            -20%

2016-09-01      Delhi          100000           100%

Growth  is multibox

If i select filter as 5 Means last 5 months

i want to highlet highest value and lowest value

Growth  =5 then

Date             Location      Sales        Growth

2016-04-01      Delhi          10000          -50%

2016-05-01      Delhi          30000           200%

2016-06-01      Delhi          45000           50%

2016-07-01      Delhi          60000           33%

2016-08-01      Delhi          50000            -20%

2016-09-01      Delhi          100000           100%

if Growth  =3 then

2016-07-01      Delhi          60000           33%

2016-08-01      Delhi          50000           -20%

2016-09-01      Delhi          100000          100%

Please do the needful

Thiru

[antoniotiman]

I need Month to simplify expressions.

See new doc

[sunny_talwar]

Check now

[antoniotiman]

See Attachment w/o Month (only Date)

[#5]

Hi Sunny,

Growth here direct column but actually it is a expression

(Curr_value-Prev_value)/Prev_Vlaue

In your expression your directly used Growth as column can you please modify with expression

Thanks

Thiru

[sunny_talwar]

So the idea is right, you just want to replace the Growth with an expression? Would you be able to share your exact expression?

[#7]

Hi Sunny,

Yes

My expression is

(Sales-Prev_sales)/Sales 

Prev_sales i have created in script using peek

Thanks

Thiru

[sunny_talwar]

May be try this as your expression

=Only({1}(Sales-Prev_sales)/Sales)

Background expression:

=If(Only((Sales-Prev_sales)/Sales) = Max(TOTAL {<Date = {"$(='>' & Date(AddMonths(Max(Date), -Max(MonthFilter))) & '<=' & Date(Max(Date)))"}>}(Sales-Prev_sales)/Sales), Green(),

If(Only((Sales-Prev_sales)/Sales) = Min(TOTAL {<Date = {"$(='>' & Date(AddMonths(Max(Date), -Max(MonthFilter))) & '<=' & Date(Max(Date)))"}>}(Sales-Prev_sales)/Sales), Red()))

[#9]

Hi Sunny,

please find the below  attachments for smaple data which i am using.

Please modify it and do the needful

Thanks

Thiru

[#10]

Hi Antonio,

Your script is working fine.

But i have few modications in that

1.I have forecast data also in my actual application. so i can't use directly max(date) because it will take my forecast max date so i have created a variable for my maximum date which is in databbase

i.e $((V_DB_Max_date))

your code

If(Max({<Date={'>=$(=MonthStart(Max(Date),-vInput))'}>} TOTAL Aggr(Growth,Date))=Growth,lightGreen(),

If(Min({<Date={'>=$(=MonthStart(Max(Date),-vInput))'}>} TOTAL Aggr(Growth,Date))=Growth,lightRed()))

My code

If(Max({<Yearrrr={'>=$(=MonthStart($(V_DB_Max_date),-Base_Growth))'}>} TOTAL Aggr(column(4),Yearrrr))=column(4),lightGreen(),

If(Min({<Yearrrr={'>=$(=MonthStart($(V_DB_Max_date),-Base_Growth))'}>} TOTAL Aggr(column(4),Yearrrr))=column(4),lightRed()))

Please modify according this

Thanks

Thiru

[antoniotiman]

See Attachment

Regards,

Antonio