Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Qlik Community
- :
- Forums
- :
- Analytics
- :
- New to Qlik Sense
- :
- How to handle zeros when Calculating % of differen...

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Anonymous

Not applicable

2017-04-18
12:50 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

How to handle zeros when Calculating % of difference between Esitmate and Actual Cost?

Hi,

I need to calculate the percentage of the difference between Estimated Price and Actual Cost.

My current calculation works fine as long as the estimated Price is >0:

This is the calculation i used for "% Difference from Est" column:

*SUM({$<[Actual Cost]={">0"}>}[Actual Cost]-[Estimated Cost])*

*/ *

*SUM({$<[Actual Cost]={">0"}>}[Estimated Cost])*

(the set analysis is there because I only wanted the calculation to work for items that were already purchased)

I know my calculation returns "-" because Estimated Cost is zero but I don't know how to change it so that it returns 100% when the Estimated Cost is zero but the Actual Cost is positive. Can you please help me?

472 Views

5 Replies

OmarBenSalem

MVP

2017-04-18
12:54 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Try sthing like this,

if(sum("Actual Cost")=0,1,

*(SUM([Actual Cost])- SUM([Estimated Cost]))*

*/*

*SUM([Estimated Cost])*

383 Views

Anonymous

Not applicable

2017-04-18
01:51 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

try:

alt(

*SUM({$<[Actual Cost]={">0"}>}[Actual Cost]-[Estimated Cost])*

*/ *

*SUM({$<[Actual Cost]={">0"}>}[Estimated Cost]),*

*1*

*)*

*---------------------------*

383 Views

Anonymous

Not applicable

2017-04-18
03:56 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Thanks Omar and Robin,

These options work great for cases where Actual Cost >0 and Estimated Cost = 0.

However they also show 100% for cases where both Actual Cost and Estimated Cost equal zero.

Before, such lines (where both values = 0) were suppressed because I unticked the "show zero values" but this change makes them reappear since I now have 100% against these lines.

Any other option?

383 Views

Anonymous

Not applicable

2017-04-19
02:35 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

if(

not isnull(SUM({$<[Actual Cost]={">0"}>}[Actual Cost]-[Estimated Cost])/SUM({$<[Actual Cost]={">0"}>}[Estimated Cost])),

SUM({$<[Actual Cost]={">0"}>}[Actual Cost]-[Estimated Cost])/SUM({$<[Actual Cost]={">0"}>}[Estimated Cost]),

if(

(SUM({$<[Actual Cost]={">0"}>}[Actual Cost])=0) and ((SUM({$<[Actual Cost]={">0"}>}[Estimated Cost])=0)),0,1

)

)

383 Views

OmarBenSalem

MVP

2017-04-19
04:13 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

maybe

if(sum("Actual Cost")=0 and sum* ([Estimated Cost])*=0,0,

if(sum("Actual Cost")=0 and sum* ([Estimated Cost])>*0,1,

*(SUM([Actual Cost])- SUM([Estimated Cost]))*

*/*

*SUM([Estimated Cost])*

383 Views

Community Browser