topic Re: First non null value, regardless selection in QlikView

First non null value, regardless selection

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I've attached an app sample and data file.

In the app I explain the problem:

I want to get the first non null value, even if the first month of the monthyear selection, does not have value.

Example:

- if I select from fev-2017 to mai-2017, the first non null value is 14079,37. Which is correct. And I want to repeat that number in every month.

. If I select from jan-2017 to mai-2017, the first non null value is still 14079,37. Even though there is no vaue in jan. So I want to repeat that number in every month too.

Bottom of line, regardless the first month I select have or not value, I want to get the first non null value and repeat it in every month after.

Image below is my expected result.

Thanks in advanced,

Sílvia

Re: First non null value, regardless selection

Kushal_Chawda — Wed, 21 Jun 2017 09:34:18 GMT

Data:

LOAD

Code,

if(rowno()=1,Value,peek('NewValue')) as NewValue,

MonthYear,

FaseActive,

Active,

Value,

Metric,

Date,

Flag

FROM Table;

Re: First non null value, regardless selection

Anonymous — Wed, 21 Jun 2017 09:56:38 GMT

Hi Kushal,

Thanks for your reply.

But I cannot do this via script.

I need to achieve this via set analysis.

Thanks anyway.

Regards,

Sílvia

Re: First non null value, regardless selection

Kushal_Chawda — Wed, 21 Jun 2017 10:26:49 GMT

try below expression

=if(rowno()=1,sum(Value), above(total sum(Value))

Re: First non null value, regardless selection

sunny_talwar — Wed, 21 Jun 2017 11:50:37 GMT

Hey hey... I just might get a chance for another coffee here....

Try this

Avg(Aggr(FirstSortedValue({<Value = {'*'}>}TOTAL <Code> Aggr(SUM ({<

Metric = {"Sales"}, Value = {'*'}

, Active={"1"}>}Value), Code, Date), Aggr(Only(Date), Code, Date))

Count(distinct

{<Active={"1"},Flag = {'CR'}>} Code), Code, MonthYear))

Re: First non null value, regardless selection

Kushal_Chawda — Wed, 21 Jun 2017 11:58:41 GMT

Maje he bhai tere

Re: First non null value, regardless selection

Anonymous — Wed, 21 Jun 2017 14:50:16 GMT

And once again, a cup of coffee to you Sunny, thanks

With a few twists to make it work in my app, but it did worked.

Thanks again for your help.

Regards,

Sílvia

Re: First non null value, regardless selection

sunny_talwar — Wed, 21 Jun 2017 14:51:33 GMT

Awesome... how many coffee do I have so far

Re: First non null value, regardless selection

Anonymous — Wed, 21 Jun 2017 14:57:25 GMT

Not counting, but I believe that enough to stay awake at least one month straight

Thanks for the help