Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All
I have below expression , it return 0.00 on GP% measure , may i know how to make it display 19.5% ?
Hi All
I have below expression :-
(
Sum({<year = {$(=Max(if(getselectedcount(year)=0,2015,year))-0)} , month = {"<=$(=Max({<year={$(=Max(if(getselectedcount(year)=0,2015,year)))}, sales = {'*'}>} month))"}, rEVENUE={"rEVENUE"} >} Amount*1)/Rate/1000
-
Sum({<year = {$(=Max(if(getselectedcount(year)=0,2015,year))-0)} , month = {"<=$(=Max({<year={$(=Max(if(getselectedcount(year)=0,2015,year)))}, sales = {'*'}>} month))"}, cOST={"cOST"} >} Amount*-1)/Rate/1000
)
/
Sum({<year = {$(=Max(if(getselectedcount(year)=0,2015,year))-0)} , month = {"<=$(=Max({<year={$(=Max(if(getselectedcount(year)=0,2015,year)))}, sales = {'*'}>} month))"}, rEVENUE={"rEVENUE"} >} Amount*1)/Rate/1000
May i know how to make it display 19.5% ? now it display 0.00
Paul
Try this:
(Sum({<year = {$(=Max(if(getselectedcount(year)=0,2015,year))-0)} , month = {"<=$(=Max({<year={$(=Max(if(getselectedcount(year)=0,2015,year)))}, sales = {'*'}>} month))"}, rEVENUE={"rEVENUE"} >} Amount*1)/Rate/1000
-Sum({<year = {$(=Max(if(getselectedcount(year)=0,2015,year))-0)} , month = {"<=$(=Max({<year={$(=Max(if(getselectedcount(year)=0,2015,year)))}, sales = {'*'}>} month))"}, cOST={"cOST"} >} Amount*-1)/Rate/1000)/
(Sum({<year = {$(=Max(if(getselectedcount(year)=0,2015,year))-0)} , month = {"<=$(=Max({<year={$(=Max(if(getselectedcount(year)=0,2015,year)))}, sales = {'*'}>} month))"}, rEVENUE={"rEVENUE"} >} Amount*1)/Rate/1000)
my QVF file
Do you know why I cannot see your chart?
Hi Sunny
sorry i re-send the QVF file again.
Note the extra parenthesis around the divisor to ensure that the division is correct and remove the 1000 scale factor to simplify:
Num((Sum({<year = {$(=Max(if(getselectedcount(year)=0,2015,year))-0)} , month = {"<=$(=Max({<year={$(=Max(if(getselectedcount(year)=0,2015,year)))}, sales = {'*'}>} month))"}, rEVENUE={"rEVENUE"} >} Amount*1)/Rate)
-
(Sum({<year = {$(=Max(if(getselectedcount(year)=0,2015,year))-0)} , month = {"<=$(=Max({<year={$(=Max(if(getselectedcount(year)=0,2015,year)))}, sales = {'*'}>} month))"}, cOST={"cOST"} >} Amount*-1)/Rate)
/
(Sum({<year = {$(=Max(if(getselectedcount(year)=0,2015,year))-0)} , month = {"<=$(=Max({<year={$(=Max(if(getselectedcount(year)=0,2015,year)))}, sales = {'*'}>} month))"}, rEVENUE={"rEVENUE"} >} Amount*1)/Rate), '0.0%')
You could possible also remove the Rate value if it is the same at the top and the bottom...
Hi Jonathan
Can you send the QVF file to me ? As i try your expression , i get wrong value. , i get 346K. very big value.
Paul
Hi all
Does any one able to help me check why Jonathan expression look correct but return wrong GP% ?
Paul
Try this:
(Sum({<year = {$(=Max(if(getselectedcount(year)=0,2015,year))-0)} , month = {"<=$(=Max({<year={$(=Max(if(getselectedcount(year)=0,2015,year)))}, sales = {'*'}>} month))"}, rEVENUE={"rEVENUE"} >} Amount*1)/Rate/1000
-Sum({<year = {$(=Max(if(getselectedcount(year)=0,2015,year))-0)} , month = {"<=$(=Max({<year={$(=Max(if(getselectedcount(year)=0,2015,year)))}, sales = {'*'}>} month))"}, cOST={"cOST"} >} Amount*-1)/Rate/1000)/
(Sum({<year = {$(=Max(if(getselectedcount(year)=0,2015,year))-0)} , month = {"<=$(=Max({<year={$(=Max(if(getselectedcount(year)=0,2015,year)))}, sales = {'*'}>} month))"}, rEVENUE={"rEVENUE"} >} Amount*1)/Rate/1000)
Hi Sunny
Finally it work.
I still unable to duplicate how you make it , it look simple , but not , i need to a cool mind to figure out why ?
Paul
I just copy and pasted the two expressions from (GP) / (Sales) (Note the parenthesis around the Sales's expression in the denominator)