topic Re: delta error with null or zero values in QlikView

delta error with null or zero values

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: delta error with null or zero values

maxgro — Sat, 12 Mar 2016 19:27:28 GMT

with this small test script

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

Re: delta error with null or zero values

Sat, 12 Mar 2016 21:20:05 GMT

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?

Re: delta error with null or zero values

swuehl — Sun, 13 Mar 2016 00:09:47 GMT

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()

)

Re: delta error with null or zero values

Sun, 13 Mar 2016 09:52:37 GMT

Perfect work! Great!!!

Re: delta error with null or zero values

oknotsen — Sun, 13 Mar 2016 10:01:09 GMT

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 .

Re: delta error with null or zero values

Sun, 13 Mar 2016 11:25:55 GMT

Ok sorry!

Re: delta error with null or zero values

oknotsen — Sun, 13 Mar 2016 15:56:30 GMT

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.