Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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
What changes from the last thread to this one Thiru?
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
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
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?
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
I see the issue now. Will have to play around with this to have a solution for you. Give me some time