Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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))
Data:
LOAD
Code,
if(rowno()=1,Value,peek('NewValue')) as NewValue,
MonthYear,
FaseActive,
Active,
Value,
Metric,
Date,
Flag
FROM Table;
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
try below expression
=if(rowno()=1,sum(Value), above(total sum(Value))
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))
Maje he bhai tere
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
Awesome... how many coffee do I have so far
Not counting, but I believe that enough to stay awake at least one month straight
Thanks for the help