Solved: Re: highleting the background color is not working... - Qlik Community

Report Inappropriate Content · ‎2016-10-25

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

Report Inappropriate Content · ‎2016-10-25

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

View solution in original post

jyothish8807 · ‎2016-10-25

Hi Thiru,

Try like this:

if(Match(vInput,12),if(Aggr(rank(Sum(Sales)-Sum(Prev_Sales)),Date)=vKC,lightred(),if(Aggr(rank(Sum(Sales)-Sum(Prev_Sales)),Date)=1,LightGreen())))

VKC= =max({<Date=>}aggr(rank(Sum({<Date=>}Sales)-Sum({<Date=>}Prev_Sales)),Date))

For 24 and 36 months make rank >2 and create a new variable vKC 2 = vKC-1 and use them.

Also pfa;

Regards

KC

Best Regards,
KC

jyothish8807 · ‎2016-10-25

Here you go:

pick(Match(vInput,12,24,36),if(Aggr(rank(Sum(Sales)-Sum(Prev_Sales)),Date)=vKC,lightred(),if(Aggr(rank(Sum(Sales)-Sum(Prev_Sales)),Date)=1,LightGreen())),if(Aggr(rank(Sum(Sales)-Sum(Prev_Sales)),Date)<=2,lightgreen(),

if(Aggr(rank(Sum(Sales)-Sum(Prev_Sales)),Date)>=vKC-1,lightred())),if(Aggr(rank(Sum(Sales)-Sum(Prev_Sales)),Date)<=2,lightgreen(),

if(Aggr(rank(Sum(Sales)-Sum(Prev_Sales)),Date)>=vKC-1,lightred())))

Regards

KC

Best Regards,
KC

Report Inappropriate Content · ‎2016-10-25

Hi Jyothish,

Thanks for you reply.

I guess you are not clear with the requirement.

Requirement is i need to high let back ground of min and max values yearly i.e. 12 months.

If i given in put as 12

means from latest month to last 12 months

highlet min and max values

If i given in put as 24(which is l2 years)

means from latest month to last 12 months

highlet min and max values

and

highlet 12 th month to 24 month

max and min values

SO total 2 min and 2 max values

same for 36 3 min and 3 max values

Please check Growth column background color while giving input 12,24 and 36

Thanks

Thiru

sunny_talwar · ‎2016-10-25

What changes from the last thread to this one Thiru?

jyothish8807 · ‎2016-10-25

Hi Thiru,

I believe it is working as you are expecting. Only for 36 month you need to make changes like this:

pick(Match(vInput,12,24,36),if(Aggr(rank(Sum(Sales)-Sum(Prev_Sales)),Date)=vKC,lightred(),if(Aggr(rank(Sum(Sales)-Sum(Prev_Sales)),Date)=1,LightGreen())),if(Aggr(rank(Sum(Sales)-Sum(Prev_Sales)),Date)<=2,lightgreen(),

if(Aggr(rank(Sum(Sales)-Sum(Prev_Sales)),Date)>=vKC-1,lightred())),if(Aggr(rank(Sum(Sales)-Sum(Prev_Sales)),Date)<=3,lightgreen(),

if(Aggr(rank(Sum(Sales)-Sum(Prev_Sales)),Date)>=vKC-2,lightred())))

Regards

KC

Best Regards,
KC

Report Inappropriate Content · ‎2016-10-25

Hi Sunny,

I have chnaged the formula

earliear we are using

(sum(sales)-sum(Prev_sales))/sum(Prev_sales)

now

sum(sales)-sum(prev_sales)

Thanks

thiru

sunny_talwar · ‎2016-10-25

For the color you changed the expression? How does it matter if you use

(sum(sales)-sum(Prev_sales))/sum(Prev_sales)

or

sum(sales)-sum(prev_sales)

for color? I mean won't they be in generally the same direction?

Which expression am I looking at? Growth or Growth_test?

Report Inappropriate Content · ‎2016-10-25

Hi sunny,

Growth is what you provided and which is working fine with

(sum(sales)-sum(Prev_sales))/sum(Prev_sales)

And Growth_test i have changed the formula to

sum(sales)-sum(prev_sales)

Which is not working.

Please modify the Growth_test background formaula

Thiru

sunny_talwar · ‎2016-10-25

I see the issue now. Will have to play around with this to have a solution for you. Give me some time

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