Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
sunny_talwar

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
Kushal_Chawda

Data:

LOAD

Code,

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

MonthYear,

FaseActive,

Active,

Value,

Metric,

Date,

Flag

FROM Table;

Anonymous
Not applicable
Author

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

Kushal_Chawda

try below expression

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

sunny_talwar

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

Kushal_Chawda

Maje he bhai tere

Anonymous
Not applicable
Author

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

sunny_talwar

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

Anonymous
Not applicable
Author

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

Thanks for the help