Solved: Re: incorrect % value given - Qlik Community

Report Inappropriate Content · ‎2017-02-22

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 !

PrashantSangle · ‎2017-02-23

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,

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

View solution in original post

sunny_talwar · ‎2017-02-22

Try now:

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

sunny_talwar · ‎2017-02-22

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

Report Inappropriate Content · ‎2017-02-22

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

sunny_talwar · ‎2017-02-22

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

Report Inappropriate Content · ‎2017-02-22

the calculated value will be over 80% if done individually

sunny_talwar · ‎2017-02-22

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

Report Inappropriate Content · ‎2017-02-22

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

sunny_talwar · ‎2017-02-22

Unless you can share your application, I would really know

Preparing examples for Upload - Reduction and Data Scrambling

Uploading a Sample

Report Inappropriate Content · ‎2017-02-22

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