Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

First non null value, regardless selection

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.

expected.png

Thanks in advanced,

Sílvia

1 Solution

Accepted Solutions
Highlighted

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

View solution in original post

8 Replies
Highlighted
MVP
MVP

Data:

LOAD

Code,

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

MonthYear,

FaseActive,

Active,

Value,

Metric,

Date,

Flag

FROM Table;

Highlighted
Creator II
Creator II

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

Highlighted
MVP
MVP

try below expression

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

Highlighted

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

View solution in original post

Highlighted
MVP
MVP

Maje he bhai tere

Highlighted
Creator II
Creator II

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

Highlighted

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

Highlighted
Creator II
Creator II

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

Thanks for the help