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

[sunny_talwar]

I am not sure I follow? Just because Expression Editor is saying that there is an error, doesn't mean there is an error. As long as you are getting the desired result, how does it matter to have any error or no error?

If you really don't wish to see the error, you can update to a newer version of QlikView where this issue was resolved. I am using QV 12 SR5 and I don't see any error

[#3 · Accepted solution]

Hi Sunny,

I got the solution.

Problem is the listing the months

based on the our code

0 to 12

12 to 24

24 to 36

so if a negative value present in 12 or 24 it is repeating,so i given

0 to 12

13 to 24

25 to 36

so that it will be unique

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

so if we chnage>= to >

then it will work

Thanks Sunny.

Thiru

[sunny_talwar]

I am still not sure what it does, but I am glad it did work for you.

Best,

Sunny