topic Re: incorrect % value given in QlikView

incorrect % value given

Wed, 22 Feb 2017 09:34:19 GMT

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

sunny_talwar — Wed, 22 Feb 2017 09:39:16 GMT

Try now:

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

Re: incorrect % value given

sunny_talwar — Wed, 22 Feb 2017 09:40:23 GMT

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

Re: incorrect % value given

Wed, 22 Feb 2017 09:42:46 GMT

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

Re: incorrect % value given

sunny_talwar — Wed, 22 Feb 2017 09:43:51 GMT

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

Re: incorrect % value given

Wed, 22 Feb 2017 09:48:29 GMT

the calculated value will be over 80% if done individually

Re: incorrect % value given

sunny_talwar — Wed, 22 Feb 2017 09:50:19 GMT

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

Re: incorrect % value given

Wed, 22 Feb 2017 09:58:14 GMT

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

sunny_talwar — Wed, 22 Feb 2017 09:59:58 GMT

Unless you can share your application, I would really know

Preparing examples for Upload - Reduction and Data Scrambling

Uploading a Sample

Re: incorrect % value given

Thu, 23 Feb 2017 03:54:49 GMT

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

PrashantSangle — Thu, 23 Feb 2017 06:01:33 GMT

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

sunny_talwar — Thu, 23 Feb 2017 10:11:46 GMT

dreamer4‌ 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?