Discussion board where members can get started with Qlik Sense.
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
Solved! Go to Solution.
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
)
)
)
)
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
)
)
)
)
If your question has been answered, please don't forget to close the thread by marking the correct answer as so.
Thanks,
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?
Can you share the qvf file (your app) you're working with?
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..
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 :