11 Replies Latest reply: Feb 23, 2017 5:12 AM by Sunny Talwar

incorrect % value given

Dear experts,

I am trying to get a % value out of an expression, however I do believe there is some faults with my coding. Below I will provide the expression as well as the logic I am trying to achieve.

the requirements: (revenue from previous year / revenue this year) - 1

Formula/Script:

(Sum({<BDF_Type = {"Revenue"}, [year] = {"\$(=Max([year])-1"}>} cash)    /      Sum({<BDF_Type = {"Revenue"}, [year] = {"\$(=Max([year])"}>} cash) ) -1

The individual components of expression work, however when put together to acquire the ratio, they seem to be calculating the wrong values. Cheers !

• Re: incorrect % value given

Try now:

(Sum({<BDF_Type = {"Revenue"}, [year] = {"\$(=Max([year])-1)"}>} cash)/Sum({<BDF_Type = {"Revenue"}, [year] = {"\$(=Max([year]))"}>} cash)) - 1

• Re: incorrect % value given

But I wonder how this was working individually.... was this a typo?

• Re: incorrect % value given

yes it was a typo when i posted the question sorry !

• Re: incorrect % value given

I don't see anything wrong in your expression, would you be able to share a sample to look at?

• Re: incorrect % value given

the calculated value will be over 80% if done individually

• Re: incorrect % value given

you don't have year set analysis in the denominator, could that be the issue?

• Re: incorrect % value given

sorry when I changed the denominator to Sum({<BDF_Type = {"Revenue"}, [year] = {"\$(=Max([year])"}>} cash) instead of using the one shown in the picture i get 100% which is weird

• Re: incorrect % value given

Unless you can share your application, I would really know

Preparing examples for Upload - Reduction and Data Scrambling

• Re: incorrect % value given

Heya Sunny,

This is the file as per requested! sorry I took so long to get back to you, had some trouble with my computer. Is it possible to view the files once you have done the edit as I am using the free personal version at the moment!

Kind regards,

Royce Tang

• Re: incorrect % value given

Hi,

In your denominator expression you have written same expression as numerator that why you are getting 100 % Check again

Below expression are you looking for

=num(Sum({<BDF_Type = {"Revenue"}, [year] = {"\$(=Max([year])-1)"}>} cash)/Sum({<BDF_Type = {"Revenue"}, [year] = {"\$(=Max([year]))"}>} cash), '###.00%')

Regards,

• Re: incorrect % value given

Prashant has correctly pointed out the issue, isn't it?

Once I changed to Max(Year) in the set analysis for denominator, it seems to be all working?