Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
paulyeo11
Master
Master

GP% measure , may i know how to make it display 19.5% ?

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

1 Solution

Accepted Solutions
sunny_talwar

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)

View solution in original post

11 Replies
paulyeo11
Master
Master
Author

my QVF file

sunny_talwar

Do you know why I cannot see your chart?

Capture.PNG

paulyeo11
Master
Master
Author

Hi Sunny

sorry i re-send the QVF file again.

jonathandienst
Partner - Champion III
Partner - Champion III

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...

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
paulyeo11
Master
Master
Author

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

paulyeo11
Master
Master
Author

Hi all

Does any one able to help me check why Jonathan expression look correct but return wrong GP% ?

Paul

sunny_talwar

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)

paulyeo11
Master
Master
Author

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

sunny_talwar

I just copy and pasted the two expressions from (GP) / (Sales) (Note the parenthesis around the Sales's expression in the denominator)