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
- :
- Calculated measures

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-07
09:14 AM

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

Calculated measures

Hi guys,

it would be great if you could help me out with the following situation.

I have i.e. sales data of the years 2016, 2015 & 2014 in 2 different categories.

Now I want to calculate what their sales are going to be in 2017, based on a growth %.

I've attached a file as an example.

I need a function for the following 4 calculated measures

1 = * if Category = A, and 2014, 2015 & 2016 > 0 then AVG 3 years * 1.2

2 = * if Category = A, and revenue <0 in 2014 but > 0 in 2015,2016 - then 2016 * 1.2

3 = * if Category = A and revenue in 2016 <= 0, then show 0

4 = * if Category = A and revenue in 2015 <= 0 and revenue in 2016 > 0, then show avg 2014-2016

Could you guys help me out?

Cheers,

Mike

1 Solution

Accepted Solutions

OmarBenSalem

MVP

2017-04-07
09:47 AM

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

Based on your example data :

**if(sum({<Category={'A'}>}[Revenue 2014])>0 and sum({<Category={'A'}>}[Revenue 2015])>0 and sum({<Category={'A'}>}[Revenue 2016])>0, **

**(sum({<Category={'A'}>}[Revenue 2014])+sum({<Category={'A'}>}[Revenue 2015])+sum({<Category={'A'}>}[Revenue 2016]))/3**

**, **

**if(sum({<Category={'A'}>}[Revenue 2014])<0 and sum({<Category={'A'}>}[Revenue 2015])>0 and sum({<Category={'A'}>}[Revenue 2016])>0,**

**sum({<Category={'A'}>}[Revenue 2016])*1.2,**

**if(sum({<Category={'A'}>}[Revenue 2016])<=0 ,0,**

**if(sum({<Category={'A'}>}[Revenue 2015])<=0 and sum({<Category={'A'}>}[Revenue 2016])>0,**

**(sum({<Category={'A'}>}[Revenue 2014])+sum({<Category={'A'}>}[Revenue 2016]))/2**

**)**

**)**

**)**

**)**

6 Replies

OmarBenSalem

MVP

2017-04-07
09:47 AM

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

Based on your example data :

**if(sum({<Category={'A'}>}[Revenue 2014])>0 and sum({<Category={'A'}>}[Revenue 2015])>0 and sum({<Category={'A'}>}[Revenue 2016])>0, **

**(sum({<Category={'A'}>}[Revenue 2014])+sum({<Category={'A'}>}[Revenue 2015])+sum({<Category={'A'}>}[Revenue 2016]))/3**

**, **

**if(sum({<Category={'A'}>}[Revenue 2014])<0 and sum({<Category={'A'}>}[Revenue 2015])>0 and sum({<Category={'A'}>}[Revenue 2016])>0,**

**sum({<Category={'A'}>}[Revenue 2016])*1.2,**

**if(sum({<Category={'A'}>}[Revenue 2016])<=0 ,0,**

**if(sum({<Category={'A'}>}[Revenue 2015])<=0 and sum({<Category={'A'}>}[Revenue 2016])>0,**

**(sum({<Category={'A'}>}[Revenue 2014])+sum({<Category={'A'}>}[Revenue 2016]))/2**

**)**

**)**

**)**

**)**

OmarBenSalem

MVP

2017-04-07
10:19 AM

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

If your question has been answered, please don't forget to close the thread by marking the correct answer as so.

Thanks,

454 Views

Anonymous

Not applicable

2017-04-07
10:23 AM

Author

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

Thanks for your reply Omar! I was still testing haha. I will mark the correct answer if I have everything working!:)

Unfortunately, I'm still getting NULL values when I'm using the next part of your code.

**(if(sum({<Category={'A'}>}[Revenue 2014])<0**

**and sum({<Category={'A'}>}[Revenue 2015])>0**

**and sum({<Category={'A'}>}[Revenue 2016])>0,**

**sum({<Category={'A'}>}[Revenue 2016])*1.2)**

And what should I do if I want to add the same formulas for Category B to this formula, but with category B * 1.1?

454 Views

OmarBenSalem

MVP

2017-04-07
10:43 AM

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

Can you share the qvf file (your app) you're working with?

454 Views

Anonymous

Not applicable

2017-04-07
10:46 AM

Author

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

I can't do that unfortunately, because the data that we use is confidential for our company..

What I'm searching for, is a formula that says what you already explained, but with a second if statement (if category = B). That could be the same formula, but I can't get the multiple if statements working.

Sorry for the inconvenience..

454 Views

OmarBenSalem

MVP

2017-04-07
11:01 AM

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

You simply choose a chart,

as dimension: category:

as measure:

if(sum([Revenue 2014])>0 and sum([Revenue 2015])>0 and sum([Revenue 2016])>0,

(sum([Revenue 2014])+sum([Revenue 2015])+sum([Revenue 2016]))/3

,

if(sum([Revenue 2014])<0 and sum([Revenue 2015])>0 and sum([Revenue 2016])>0,

sum([Revenue 2016])*1.2,

if(sum([Revenue 2016])<=0 ,0,

if(sum([Revenue 2015])<=0 and sum([Revenue 2016])>0,

(sum([Revenue 2014])+sum([Revenue 2016]))/2

)

)

)

)

result :

I'm working with :