Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Calculated Change in a numerical value and not a %

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.

1 Solution

Accepted Solutions
Oleg_Troyansky

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!

View solution in original post

6 Replies
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

Not applicable
Author

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

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
Creator II
Creator II

Hi Silvia,

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

rahulpawarb
Specialist III
Specialist III

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

Not applicable
Author

Thank you all so much,  that really helped!