First non null value, regardless selection

[Anonymous]

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

[Kushal_Chawda]

Data:

LOAD

Code,

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

MonthYear,

FaseActive,

Active,

Value,

Metric,

Date,

Flag

FROM Table;

[Anonymous]

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 · Accepted solution]

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]

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 counting, but I believe that enough to stay awake at least one month straight

Thanks for the help