topic Re: Percentages in expressions in Qlikview in App Development

Percentages in expressions in Qlikview

CSmith — Wed, 23 Feb 2022 20:15:23 GMT

I can't get all of the percentages to display correctly in my report. This is my formula;

=((sum({<MTH={'January'}>}sold)+ sum({<MTH={'February'}>}sold)+ sum({<MTH={'March'}>}sold)/sum({<MTH={'January'}, year={$(=GetFieldSelections(year)-1)}>} sold)+sum({<MTH={'February'}, year={$(=GetFieldSelections(year)-1)}>} sold)+sum({<MTH={'March'}, year={$(=GetFieldSelections(year)-1)}>}sold))-1

Number field is: Fixed to and Format Pattern is #,##0%;(#,##0%)

Some of the percentages seems to displaying correctly while some of the others are not.

I have attached some of my results for review. Can anyone tell me what I am doing wrong??

Re: Percentages in expressions in Qlikview

MarcoWedel — Wed, 23 Feb 2022 21:01:38 GMT

please explain how you calculated your expected results.

Re: Percentages in expressions in Qlikview

CSmith — Thu, 24 Feb 2022 12:37:16 GMT

Expected results were calculate. See below example;

sold items for January (2021)+ sold items for February (2021) + sold items for March (2021) / sold items for January (2020) + sold items for February (2020) + sold items for March (2020) then 1 was substracted from that number

385+629+1,116 = 2130 / 995+1,770+2,172 = 4937 = 0.431436-1 = -0.568 (57%)

Hopefully this helps.

Re: Percentages in expressions in Qlikview

MarcoWedel — Thu, 24 Feb 2022 19:37:59 GMT

Could you please explain for rows 3 and 6 (yellow) in your example as well (best using separate steps)?

thanks

Marco

Re: Percentages in expressions in Qlikview

CSmith — Thu, 24 Feb 2022 20:13:04 GMT

What I am trying to accomplish is the difference in sales for a particular company, in a percentage format, between the first quarter of 2021 and the first quarter of 2020.

Jan(21), Feb(21) and Mar(21) are being added together. That number will be divided by the number you get from adding Jan(20), Feb(20) and Mar(20). That number will be substracted from 1. That number should be displayed in % format. The parentheses and the number being displayed in red denotes that is a negative percentage amount.

0 + 0 + 0 = 0 / 6 + 0 + 0 = 6; 0/6 = 0; 0 - 1 = -1. I would expect the answer for Quarter % YOY to be (1%) not (100%)

Jan(21), Feb(21) and Mar(21) are being added together. That number will be divided by the number you get from adding Jan(20), Feb(20) and March (20). That number will be substracted from 1. That number should be displayed in % format. The parentheses and the number being displayed in red denotes that is a negative percentage amount.

7 + 5 + 5 = 17 / 1 + 0 + 0 = 1; 17/1 = 17; 17 -1 = 16. I would expect the answer for Quarter % YOY to be (16%) not (1600%)

I hope this helps.

Re: Percentages in expressions in Qlikview

MarcoWedel — Thu, 24 Feb 2022 22:25:37 GMT

Why do you accept a result of -0.568 to represent -57%, when at the same time you expect -1 to be -1% and -16 to be -16% ?

Re: Percentages in expressions in Qlikview

CSmith — Fri, 25 Feb 2022 12:40:49 GMT

Thank you. I figured it out.