# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Highlighted
Contributor II

## 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

Tags (2)
1 Solution

Accepted Solutions Partner

## Re: Calculated measures

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 Partner

## Re: Calculated measures

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

)

)

)

) Partner

Thanks,

Contributor II

## Re: Calculated measures

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? Partner

## Re: Calculated measures

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

Contributor II

## Re: Calculated measures

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.. Partner

## Re: Calculated measures

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 :  