Sublist Function: Unable to retrieve data wrt Year

Join the discussion on using Formulas and Functions in Quantrix

Moderator: Moderators

Sublist Function: Unable to retrieve data wrt Year

Postby ShriShank » Thu Aug 31, 2017 12:48 pm

Hello,

I am trying to retrieve YTD revenue values from LayoutofMonth matrix to Final View Matrix.

I used following formula:
Revenue=sum(sublist(LayoutofMonth::Year:Month:Revenue,1,#Month))

The formula works for the first year 2018. But, I am unable to retrieve the value of revenue from the next year onwards e.g 2019

What am I missing??
You do not have the required permissions to view the files attached to this post.
ShriShank
Quantrix Forum Member
 
Posts: 27
Joined: Mon Feb 13, 2017 9:25 am

Re: Sublist Function: Unable to retrieve data wrt Year

Postby S a u » Thu Aug 31, 2017 9:44 pm

Hi,

Try this formula:

Revenue = sum(sublist(LayoutofMonth::Month:Revenue; 1))
skip Better Worse
Best regards,
SAU
S a u
Quantrix Forum Member
 
Posts: 43
Joined: Fri Jun 20, 2014 8:57 am

Re: Sublist Function: Unable to retrieve data wrt Year

Postby ShriShank » Fri Sep 01, 2017 8:35 am

Thanks for the reply.
But my aim with this model is to get the sum of the revenue(Year to date) from the starting fiscal year month of Oct to the month selected from the drop down menu in the category month.
And when I change the year it should sum again from oct for that year to the month selected.
e.g
If i chose month June, then for year 2018 the actual should be $40
and for year 2019 it should show actual as $6000
ShriShank
Quantrix Forum Member
 
Posts: 27
Joined: Mon Feb 13, 2017 9:25 am

Re: Sublist Function: Unable to retrieve data wrt Year

Postby ShriShank » Fri Sep 01, 2017 8:49 am

When is use the following formula:
Revenue=sum(sublist(LayoutofMonth::Year:Month:Revenue,1,#Year:Month))
skip Better Worse,%// using Layoutof Analysis::Year as Year

I can get the summation of actual wrt month and year but I cannot change the starting month to oct of specified year. It still refers to Oct of 2018.

when i change the formula to:
Revenue=sum(sublist(LayoutofMonth::Year:Month:Revenue,#Year:Month:Oct,#Year:Month))
skip Better Worse,%// using Layoutof Analysis::Year as Year

I get #Bounds error
ShriShank
Quantrix Forum Member
 
Posts: 27
Joined: Mon Feb 13, 2017 9:25 am

Re: Sublist Function: Unable to retrieve data wrt Year

Postby ShriShank » Fri Sep 01, 2017 10:37 am

,y quick fix:
Revenue=sum(sublist(LayoutofMonth::Year:Month:Revenue,switch(@Year="2018",1,@Year="2019",13,@Year="2020",25,@Year="2021","37",1,),#Year:Month))
skip Better Worse,%// using Layoutof Analysis::Year as Year

Let me know if there is a better way to optimize this
ShriShank
Quantrix Forum Member
 
Posts: 27
Joined: Mon Feb 13, 2017 9:25 am


Return to Formulas and Functions

Who is online

Users browsing this forum: No registered users and 3 guests