Calculated Change in a numerical value and not a %

[#1]

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.

[sunny_talwar]

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

[#3]

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%

[Oleg_Troyansky · Accepted solution]

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!

[ganeshsvm]

Hi Silvia,

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

[rahulpawarb]

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

[#7]

Thank you all so much,  that really helped!