highleting the background color is not working when i change the formula

[#1]

Hi

I have a 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

like that  36 also

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

12 months   --- 1 min and 1 max

24 months   -- 2 min and 2 max

36 months   --  2 min and 2 max

here attahced qvw file is having the code it's working for

growth=(sales-prev_sales)/Prev_sales

when i change the formula

growth= sales-Prev_sales  it is not working you can see growth_test bacjkgorun formlua

Please do the needful

Thiru

[#2]

ok Sunny,

Thanks

[#3]

Hi Sunny,

As of now if possible provide me the solution how to high let max and min values year wise ,Because

now no formula i am able to create a separate column using script i.e.Slaes_growth which is

Now only one column

Thanks

Thiru

[sunny_talwar]

Not sure I understand your above comments Thiru

[sunny_talwar]

See if this expression helps:

If(Max({<Date={'>=$(=AddMonths(Date(V_DB_Max_date),-12))<=$(=Date(V_DB_Max_date))'}>} TOTAL Aggr((Sum(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))

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

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

,lightGreen(),

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

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

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

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

,lightRed()))

[#6]

Hi Sunny,

Please check formula is having error

Thiru

[#7]

If(Max({<Date={'>=$(=AddMonths(Date(V_DB_Max_date),-12))<=$(=Date(V_DB_Max_date))'}>} TOTAL Aggr((Sum(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))

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

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

,lightGreen(),

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

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

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

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

,lightRed()))

[sunny_talwar]

What is the error?

[#9]

Hi Sunny,

If you see the above code which i highleted is bold i.e. date is having date-

Thiru

[sunny_talwar]

Yes, I did add that in there. What is the issue? The chart is still showing the right colors in the image above? Is it not showing right colors at your end?

[#11]

Hi Sunny,

I guess issue is with negative values i face same problem in earlier also when we are using aggr function.

Please think in that angle also then we will get the answer.

Just wondered,the code is having issue but still owrks some time your qvw file:)

Thiru