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

[#2]

Hi Antonio,

It's showing only max value not showing min value in this attachment.

For getting min value we need to add one more entry?

Thanks

Thiru

[antoniotiman]

In my doc (min 0, max 7.5) : Max Date/Month - 4

[#4]

Hi Antonio,

can you please set the max date is dec2015 and see the results,it is varying

Thanks

Thiru

[antoniotiman]

You wrote

'variable for my maximum date which is in databbase'

Where You are setting this variable ?

In my doc variable is 'maximum date which is in databbase',

see script.

[#6]

Hi Antonio,

Yes you are right,but i guess if you have more values in the list then it is varying

example

if i run same for me it is taking min value from existing list and max value is from forecast values.

So please add 5 or 10 values manually  to your list

i mean from oct-2016 to oct-2017 then see the values will differ

Thanks and Regards

Thiru

[#7]

Hi Antonio,

Or even you can max date is dec-2015 then see

Thanks

Thiru

[antoniotiman]

Thiru,

Which list ?

I don't understand :

So please add 5 or 10 values manually  to your list

i mean from oct-2016 to oct-2017 then see the values will differ

Can You provide Your expected result and Your selections ?

Please, see my script.

[#9]

Hi Antonio,

Actually My scenario is i have records upto database like

up-to Sep-2016 and i have created 10 years data i.e.sep-2026

so i have created a variable for database max value i.e sep-2016 =V_DB_MAX_DATE

but your case you dont have forecast data so results are working fine(i am gussing)

so i have changed the maxdate variable in your case pleas find the attached

now max date is Apr-2016 so that results are varying.

Still you are not able to understand please let me know i will create forecast data and will attach new application

Thanks in advance for your help

Thiru

[antoniotiman]

See attachment

[#11]

Hi Antonio,

This is the problem which i am talking about

If max value is apr 2016and vinput =6 then

It needs to highlet the values

from apr-1016 to above 6 months i.e nov-2015

between these two dates it has to identify max and min values.

But in the above image 7.5 which is highleted value is out of scope for us becaues it is not in the range of nov-2015 to apr-2016

And want to understand

your code

If(Date >= AddMonths(Date(V_DB_Max_date),-vInput),

If(Max({<Date={'>=$(=AddMonths(Date(V_DB_Max_date),-vInput))'}>} TOTAL Aggr((Sum(Sales)-Sum(Prev_Sales))/Sum(Prev_Sales),Date))=(Sum(Sales)-Sum(Prev_Sales))/Sum(Prev_Sales),lightGreen(),

If(Min({<Date={'>=$(=AddMonths(Date(V_DB_Max_date),-vInput))'}>} TOTAL Aggr((Sum(Sales)-Sum(Prev_Sales))/Sum(Prev_Sales),Date))=(Sum(Sales)-Sum(Prev_Sales))/Sum(Prev_Sales),lightRed())))

here       " Date >= AddMonths(Date(V_DB_Max_date),-vInput) "

This one i believe

" Date >= AddMonths(Date(V_DB_Max_date),-vInput)  and Date <= $(V_DB_Max_date)

Because always we want to highlet max and min the values between   $(V_DB_Max_date)  and $(V_DB_Max_date) -Vinput

for my output screen please find the attachments.

Thiru