topic Re: Calculated Change in a numerical value and not a % in QlikView

Calculated Change in a numerical value and not a %

Wed, 11 Jan 2017 21:55:11 GMT

Hi:

I have the following calculation and I need it to show an actual value rather than a %.

=Num(Sum({<[Issue - Currently Identified By (Issue Inventory)]={'Business'},[End Month] = {'$(=Max([End Month]))'}>} [Issue ID (Issue Inventory)])/Sum({<[Issue - Currently Identified By (Issue Inventory)]={'Business'},[End Month] = {'$(=Max([End Month]) - 1)'}>} [Issue ID (Issue Inventory)])- 1, '0%')

Any help would be greatly appreciated.

Re: Calculated Change in a numerical value and not a %

sunny_talwar — Wed, 11 Jan 2017 23:57:38 GMT

You want to see this percentage in decimals? May be this

=Sum({<[Issue - Currently Identified By (Issue Inventory)]={'Business'},[End Month] = {'$(=Max([End Month]))'}>} [Issue ID (Issue Inventory)])/Sum({<[Issue - Currently Identified By (Issue Inventory)]={'Business'},[End Month] = {'$(=Max([End Month]) - 1)'}>} [Issue ID (Issue Inventory)]) - 1

Re: Calculated Change in a numerical value and not a %

Thu, 12 Jan 2017 00:34:41 GMT

Hi Sunny

No, I would like to see the number as a whole number, increase or decrease. Is that possible, no matter what I try I only get decimals or%

Re: Calculated Change in a numerical value and not a %

Oleg_Troyansky — Thu, 12 Jan 2017 01:56:08 GMT

Hi Silvia,

your formula is calculating the % change from prior month. Using pseudo-code, it looks like this:

[ThisMonth] / [Prior Month] - 1 , which is the same as:

([This Month] - [Prior Month]) / [Prior Month]

What you are asking to show is the pure difference between This Month and Prior Month. So, the formula should look like this:

=Num(

Sum({<[Issue - Currently Identified By (Issue Inventory)]={'Business'},[End Month] = {'$(=Max([End Month]))'}>} [Issue ID (Issue Inventory)])

Sum({<[Issue - Currently Identified By (Issue Inventory)]={'Business'},[End Month] = {'$(=Max([End Month]) - 1)'}>} [Issue ID (Issue Inventory)])

, '0')

cheers,

Oleg Troyansky

Upgrade your Qlik skills at the Masters Summit for Qlik - coming soon to Munich, Germany!

Re: Calculated Change in a numerical value and not a %

ganeshsvm — Thu, 12 Jan 2017 04:29:30 GMT

Hi Silvia,

You can also use floor() or Ceil() instead of Num() to round off the decimal values as per your requirement.

Re: Calculated Change in a numerical value and not a %

rahulpawarb — Thu, 12 Jan 2017 04:46:57 GMT

Hello Silvia,

Hope you are doing good.

Please use below expression to get expected results (I have modified the expression to add/modify the bold & underlined part).

=Num(

Sum(

{<[Issue - Currently Identified By (Issue Inventory)]={'Business'},

[End Month] = {'$(=Max([End Month]))'}>}

[Issue ID (Issue Inventory)])

/Sum(

{<[Issue - Currently Identified By (Issue Inventory)]={'Business'},

[End Month] = {'$(=Max([End Month]) - 1)'}>}

[Issue ID (Issue Inventory)])- 1)

, '000,#')

Trust this will help.

Regards!

Rahul

Re: Calculated Change in a numerical value and not a %

Thu, 12 Jan 2017 11:22:25 GMT

Thank you all so much, that really helped!