Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. $300 savings extended to February 9th Learn More
Not applicable

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

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

1 Solution

Accepted Solutions
Not applicable

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

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

22 Replies
jyothish8807
Honored Contributor II

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

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
Honored Contributor II

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

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
Not applicable

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

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

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

What changes from the last thread to this one Thiru?

jyothish8807
Honored Contributor II

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

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
Not applicable

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

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

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

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?

Not applicable

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

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

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

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