highlet background color max and min values year wise

[#1]

Hi

I have scenario

i need to highlet background colur based on year

For example

12

means in 12 months one max and one min vlaue i need to highlet

24

means  i need to highlet 2 min and 2 max values

i.e. for every 12 months i need to highlet  one min and one max value

Please find the attachedcand do the needful

Thiru

[sunny_talwar]

May be try this:

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

(Sum({<Date={'>=$(=AddMonths(Date(V_DB_Max_date),-12))<=$(=Date(V_DB_Max_date))'}>}Sales)-Sum({<Date={'>=$(=AddMonths(Date(V_DB_Max_date),-12))<=$(=Date(V_DB_Max_date))'}>}Prev_Sales))/Sum({<Date={'>=$(=AddMonths(Date(V_DB_Max_date),-12))<=$(=Date(V_DB_Max_date))'}>}Prev_Sales)

or If(Match(vInput, 24, 36), Max({<Date={'>=$(=AddMonths(Date(V_DB_Max_date),-24))<=$(=AddMonths(Date(V_DB_Max_date),-13))'}>} TOTAL Aggr((Sum(Sales)-Sum(Prev_Sales))/Sum(Prev_Sales),Date))=

(Sum({<Date={'>=$(=AddMonths(Date(V_DB_Max_date),-24))<=$(=AddMonths(Date(V_DB_Max_date),-13))'}>}Sales)-Sum({<Date={'>=$(=AddMonths(Date(V_DB_Max_date),-24))<=$(=AddMonths(Date(V_DB_Max_date),-13))'}>}Prev_Sales))/Sum({<Date={'>=$(=AddMonths(Date(V_DB_Max_date),-24))<=$(=AddMonths(Date(V_DB_Max_date),-13))'}>}Prev_Sales))

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

(Sum({<Date={'>=$(=AddMonths(Date(V_DB_Max_date),-36))<=$(=AddMonths(Date(V_DB_Max_date),-25))'}>}Sales)-Sum({<Date={'>=$(=AddMonths(Date(V_DB_Max_date),-36))<=$(=AddMonths(Date(V_DB_Max_date),-25))'}>}Prev_Sales))/Sum({<Date={'>=$(=AddMonths(Date(V_DB_Max_date),-36))<=$(=AddMonths(Date(V_DB_Max_date),-25))'}>}Prev_Sales))

,lightGreen(),

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

(Sum({<Date={'>=$(=AddMonths(Date(V_DB_Max_date),-12))<=$(=Date(V_DB_Max_date))'}>}Sales)-Sum({<Date={'>=$(=AddMonths(Date(V_DB_Max_date),-12))<=$(=Date(V_DB_Max_date))'}>}Prev_Sales))/Sum({<Date={'>=$(=AddMonths(Date(V_DB_Max_date),-12))<=$(=Date(V_DB_Max_date))'}>}Prev_Sales)

or If(Match(vInput, 24, 36), Min({<Date={'>=$(=AddMonths(Date(V_DB_Max_date),-24))<=$(=AddMonths(Date(V_DB_Max_date),-13))'}>} TOTAL Aggr((Sum(Sales)-Sum(Prev_Sales))/Sum(Prev_Sales),Date))=

(Sum({<Date={'>=$(=AddMonths(Date(V_DB_Max_date),-24))<=$(=AddMonths(Date(V_DB_Max_date),-13))'}>}Sales)-Sum({<Date={'>=$(=AddMonths(Date(V_DB_Max_date),-24))<=$(=AddMonths(Date(V_DB_Max_date),-13))'}>}Prev_Sales))/Sum({<Date={'>=$(=AddMonths(Date(V_DB_Max_date),-24))<=$(=AddMonths(Date(V_DB_Max_date),-13))'}>}Prev_Sales))

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

(Sum({<Date={'>=$(=AddMonths(Date(V_DB_Max_date),-36))<=$(=AddMonths(Date(V_DB_Max_date),-25))'}>}Sales)-Sum({<Date={'>=$(=AddMonths(Date(V_DB_Max_date),-36))<=$(=AddMonths(Date(V_DB_Max_date),-25))'}>}Prev_Sales))/Sum({<Date={'>=$(=AddMonths(Date(V_DB_Max_date),-36))<=$(=AddMonths(Date(V_DB_Max_date),-25))'}>}Prev_Sales))

,lightRed()))

[#3]

Hi Sunny,

It's working.

Small change required.

My scenario is

6,12,18,24,20,36

6 means --last 6 months  max and min --------------- working

12 meas   -- last 12 months   max and min   -------- working

18 months  --- last 18 ,months  max and min ------- working

24 months   -- last 12 and 12 to 24 months  2 max and 2 min   ------ working

30 months     -- last 12 and 12 to 30 months   2 max and 2 min    --- not working

36  months   ---- last 12 and 12 to 24 and 24 to 36  3 max and 3 min   ---- working.

can you please modify the same for 30 months also.

Thanks in advance

Thiru

[sunny_talwar]

Would you be able to share where it is working for everything except the 30th month?

[#5]

Hi Sunny,

sorry it's working for only 12 and 24 and 36.

i want to add 1 18 and 30 as well

Thanks

Thiru

[#6]

I want add 6 months and 18 months and 30 months

[sunny_talwar]

So when for 6 months you will see 1 min and 1 max

for 12 months you will see 2min and 2 max

for 18 months 3min and 3max

....

and so on?

[#8]

Hi sunny

6 months ---- 1  min and 1 max

12 months   --- 1 min and 1 max

18 months   -- 1 min and 1 max

24 months   -- 2 min and 2 max

30 months   --  2 min and 2 max

36 months     -- 3 max and 3 min

what you provided script is right. on top of that 6 and 18 and 30 i need to add

Thanks

Thiru

[sunny_talwar · Accepted solution]

Here try this:

If(

If(Match(vInput, 6),

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

(Sum({<Date={'>=$(=AddMonths(Date(V_DB_Max_date),-6))<=$(=Date(V_DB_Max_date))'}>}Sales)-Sum({<Date={'>=$(=AddMonths(Date(V_DB_Max_date),-6))<=$(=Date(V_DB_Max_date))'}>}Prev_Sales))/Sum({<Date={'>=$(=AddMonths(Date(V_DB_Max_date),-6))<=$(=Date(V_DB_Max_date))'}>}Prev_Sales))

or

If(Match(vInput, 12, 24, 30, 36),

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

(Sum({<Date={'>=$(=AddMonths(Date(V_DB_Max_date),-12))<=$(=Date(V_DB_Max_date))'}>}Sales)-Sum({<Date={'>=$(=AddMonths(Date(V_DB_Max_date),-12))<=$(=Date(V_DB_Max_date))'}>}Prev_Sales))/Sum({<Date={'>=$(=AddMonths(Date(V_DB_Max_date),-12))<=$(=Date(V_DB_Max_date))'}>}Prev_Sales))

or

If(Match(vInput, 18),

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

(Sum({<Date={'>=$(=AddMonths(Date(V_DB_Max_date),-18))<=$(=Date(V_DB_Max_date))'}>}Sales)-Sum({<Date={'>=$(=AddMonths(Date(V_DB_Max_date),-18))<=$(=Date(V_DB_Max_date))'}>}Prev_Sales))/Sum({<Date={'>=$(=AddMonths(Date(V_DB_Max_date),-18))<=$(=Date(V_DB_Max_date))'}>}Prev_Sales))

or

If(Match(vInput, 24, 30, 36),

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

(Sum({<Date={'>=$(=AddMonths(Date(V_DB_Max_date),-24))<=$(=AddMonths(Date(V_DB_Max_date),-13))'}>}Sales)-Sum({<Date={'>=$(=AddMonths(Date(V_DB_Max_date),-24))<=$(=AddMonths(Date(V_DB_Max_date),-13))'}>}Prev_Sales))/Sum({<Date={'>=$(=AddMonths(Date(V_DB_Max_date),-24))<=$(=AddMonths(Date(V_DB_Max_date),-13))'}>}Prev_Sales))

or

If(Match(vInput, 30),

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

(Sum({<Date={'>=$(=AddMonths(Date(V_DB_Max_date),-30))<=$(=AddMonths(Date(V_DB_Max_date),-13))'}>}Sales)-Sum({<Date={'>=$(=AddMonths(Date(V_DB_Max_date),-30))<=$(=AddMonths(Date(V_DB_Max_date),-13))'}>}Prev_Sales))/Sum({<Date={'>=$(=AddMonths(Date(V_DB_Max_date),-30))<=$(=AddMonths(Date(V_DB_Max_date),-13))'}>}Prev_Sales))

or

If(Match(vInput, 36),

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

(Sum({<Date={'>=$(=AddMonths(Date(V_DB_Max_date),-36))<=$(=AddMonths(Date(V_DB_Max_date),-25))'}>}Sales)-Sum({<Date={'>=$(=AddMonths(Date(V_DB_Max_date),-36))<=$(=AddMonths(Date(V_DB_Max_date),-25))'}>}Prev_Sales))/Sum({<Date={'>=$(=AddMonths(Date(V_DB_Max_date),-36))<=$(=AddMonths(Date(V_DB_Max_date),-25))'}>}Prev_Sales))

,lightGreen(),

If(

If(Match(vInput, 6),

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

(Sum({<Date={'>=$(=AddMonths(Date(V_DB_Max_date),-6))<=$(=Date(V_DB_Max_date))'}>}Sales)-Sum({<Date={'>=$(=AddMonths(Date(V_DB_Max_date),-6))<=$(=Date(V_DB_Max_date))'}>}Prev_Sales))/Sum({<Date={'>=$(=AddMonths(Date(V_DB_Max_date),-6))<=$(=Date(V_DB_Max_date))'}>}Prev_Sales))

or

If(Match(vInput, 12, 24, 30, 36),

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

(Sum({<Date={'>=$(=AddMonths(Date(V_DB_Max_date),-12))<=$(=Date(V_DB_Max_date))'}>}Sales)-Sum({<Date={'>=$(=AddMonths(Date(V_DB_Max_date),-12))<=$(=Date(V_DB_Max_date))'}>}Prev_Sales))/Sum({<Date={'>=$(=AddMonths(Date(V_DB_Max_date),-12))<=$(=Date(V_DB_Max_date))'}>}Prev_Sales))

or

If(Match(vInput, 18),

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

(Sum({<Date={'>=$(=AddMonths(Date(V_DB_Max_date),-18))<=$(=Date(V_DB_Max_date))'}>}Sales)-Sum({<Date={'>=$(=AddMonths(Date(V_DB_Max_date),-18))<=$(=Date(V_DB_Max_date))'}>}Prev_Sales))/Sum({<Date={'>=$(=AddMonths(Date(V_DB_Max_date),-18))<=$(=Date(V_DB_Max_date))'}>}Prev_Sales))

or

If(Match(vInput, 24, 30, 36),

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

(Sum({<Date={'>=$(=AddMonths(Date(V_DB_Max_date),-24))<=$(=AddMonths(Date(V_DB_Max_date),-13))'}>}Sales)-Sum({<Date={'>=$(=AddMonths(Date(V_DB_Max_date),-24))<=$(=AddMonths(Date(V_DB_Max_date),-13))'}>}Prev_Sales))/Sum({<Date={'>=$(=AddMonths(Date(V_DB_Max_date),-24))<=$(=AddMonths(Date(V_DB_Max_date),-13))'}>}Prev_Sales))

or

If(Match(vInput, 30),

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

(Sum({<Date={'>=$(=AddMonths(Date(V_DB_Max_date),-30))<=$(=AddMonths(Date(V_DB_Max_date),-13))'}>}Sales)-Sum({<Date={'>=$(=AddMonths(Date(V_DB_Max_date),-30))<=$(=AddMonths(Date(V_DB_Max_date),-13))'}>}Prev_Sales))/Sum({<Date={'>=$(=AddMonths(Date(V_DB_Max_date),-30))<=$(=AddMonths(Date(V_DB_Max_date),-13))'}>}Prev_Sales))

or

If(Match(vInput, 36),

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

(Sum({<Date={'>=$(=AddMonths(Date(V_DB_Max_date),-36))<=$(=AddMonths(Date(V_DB_Max_date),-25))'}>}Sales)-Sum({<Date={'>=$(=AddMonths(Date(V_DB_Max_date),-36))<=$(=AddMonths(Date(V_DB_Max_date),-25))'}>}Prev_Sales))/Sum({<Date={'>=$(=AddMonths(Date(V_DB_Max_date),-36))<=$(=AddMonths(Date(V_DB_Max_date),-25))'}>}Prev_Sales))

,lightRed()))

[#10]

Hi Sunny,

Thanks for your effort.It's working

Thiru