Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
mslottje
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

1 Solution

Accepted Solutions
Partner
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

)

)

)

)

View solution in original post

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

)

)

)

)

View solution in original post

Partner
Partner

Re: Calculated measures

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

Thanks,

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

Re: Calculated measures

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

mslottje
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
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 :

Capture.PNGCapture.PNG