Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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

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

)

)

)

)

View solution in original post

6 Replies
OmarBenSalem

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

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

Thanks,

Anonymous
Not applicable
Author

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?

OmarBenSalem

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

Anonymous
Not applicable
Author

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

OmarBenSalem

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 :

Capture.PNGCapture.PNG