delta error with null or zero values

[#1]

Good morning,

you can help me solve the following problem ?

In a table I display the delta between two quantities, in current and previus year, for example:

Sheet1 - year 2015

Month	Product1	Product2
Jan	100	150
Feb	200	100
Mar	50	50

Sheet2 - Year 2016

Year	Month	Product1	Product2
2016	Jan	150	200
2016	Feb	100	120
2016	Mar	-	-

by calculating the delta I get the following result

Difference between years 2016/2015:

Month	Product1	Product2
Jan	50,00%	33,33%
Feb	-50,00%	20%
Mar	-100%	-100%

The expression used in the measurement is:

(sum(if (InYear (Anno, Today(), 0), [Product1]))-sum(if (InYear (Anno, Today(), -1), [Product1])))

/sum(if (InYear (Anno, Today(), -1),[Product1]))

How can it not be calculated when the product contains no value in that month ?

So as not to show -100 %?

Thanks for the support

[maxgro]

with this small test script

P:

load

  date(makedate(2015) + floor(rand()*730)) as Date,

  ceil(rand()*100) as Product1,

  ceil(rand()*100) as Product2

AutoGenerate 1000;

Left Join (P)

load Distinct Date, Month(Date) as Month, Year(Date) as Year

Resident P;

I think you can use a chart with this expression for Product1

(sum({$ <Year={$(=Year(Today()))}>} Product1) - sum({$ <Year={$(=Year(Today())-1)}>} Product1))

/

(sum({$ <Year={$(=Year(Today())-1)}>} Product1))

[#3]

Thanks for the reply .

I tried to apply your formula, but nothing has changed . In the months of 2016 that have no data displays -100 %.

I do not know if it's possible, but I should put an IF product1 not isnull ( ) performs the calculations otherwise put 0 . It's possible?

[swuehl · Accepted solution]

There might be different solutions possible depending on your data model etc.

This should return zero for the months with missing current year data:

=Count(if (InYear (Anno, Today(), 0), [Product1]))

So you can use this count in an if() statement to check your condition:

=If( Count(if (InYear (Anno, Today(), 0), [Product1])),

(sum(if (InYear (Anno, Today(), 0), [Product1]))-sum(if (InYear (Anno, Today(), -1), [Product1])))

/sum(if (InYear (Anno, Today(), -1),[Product1]))

,Null()

)

[#5]

Perfect work! Great!!!

[oknotsen]

If your question is answered, please flag the Correct Answer and optionally Helpful Answers.

If not, please make clear what part of your question still needs answering .

[#7]

Ok sorry!

[oknotsen]

No worries . Better clear that you have your answer instead of assuming you have the answer but are still looking for one!

As you did not add a new question, I assumed you mend to mark Stefan's reply as the Correct Answer. I made the change as this "closes" the topic.