Dynamic Skip?

Join the discussion on using Formulas and Functions in Quantrix

Moderator: Moderators

Dynamic Skip?

Postby jmayer » Thu Oct 06, 2011 2:17 pm

Hi,

I'm wondering if there is any way to set the range to be skiped over (when using the Skip operation) dynamically? I was hoping I might be able to set the range with Indirect - ie:

Well Name = Exp Well Summary::Well Name Skip Indirect("ExpWellID["&Counta(Exp Well Summary::Well Name)&"] .. ExpWellID[LAST]")

But I get a syntax error when trying to use Indirect with Skip. Is there any other way to do this?

Many thanks
jmayer
Quantrix Forum Member
 
Posts: 12
Joined: Tue Sep 27, 2011 9:18 pm

Postby Quantrix QA » Fri Oct 07, 2011 5:49 am

Hmmm - INDIRECT in a Skip will certainly throw a syntax error.

Sometimes if you post a sample model that will help visualize the scenario you are describing. There may be a different approach the forum community can consider.
Quantrix QA
 

My same wondering!

Postby ndt.cat » Wed Oct 12, 2011 5:04 am

Hi,
I have an example about financial model of power plant:
- Total operation years = 30 (so the Category "Year" have 30 items: '1','2', ..., '30')
- Loan repayment time [LRT] = 10 years
- Depreciation time of Construction [DTC] = 25 years

Problem:
In the functions of ALL ROWS in matrix "Loan Repayment Calculation" I have to write: [color="Blue"]... skip '1'..'10'[/color]
Same with matrix "Depreciation Calculation": [color="Blue"]... skip '1'..'25'[/color]

My problem is that when I change [LRT] or [DTC], I have to rewrite all of the functions above. It's very tired!
Is there any way to write functions with "dynamic skip"? (maybe like: ... skip #year = 1 to #year = [LRT] :D)

More advance: any way to create automatically the number of Items of Category "Year" when I change the Total operation years?

Thanks!
ndt.cat
Quantrix Forum Member
 
Posts: 8
Joined: Mon Oct 03, 2011 10:57 pm

Postby Quantrix QA » Wed Oct 12, 2011 7:38 am

Hi - You could like the Year category to a new matrix, then set a flag to indicate when the calculations should occur for Loan and Depreciation.

Then you can use this flag in an IF or SWITCH function, and avoid having to use a SKIP.

You can then simply change the flags to adjust the calculation.

Please see attached.
You do not have the required permissions to view the files attached to this post.
Quantrix QA
 

Postby ndt.cat » Wed Oct 12, 2011 10:10 pm

Thanks for your solution of IF and SWITCH functions!
I'm agree avoid having to use a SKIP in this case but I think it's better if we can use SKIP with CLAUSE or CONDITION :)

Please help me about automatic creating the number of Items of Year Category when I change "Total operation years"?
ndt.cat
Quantrix Forum Member
 
Posts: 8
Joined: Mon Oct 03, 2011 10:57 pm

Postby Carlos (vzla) » Tue Oct 08, 2013 1:17 pm

Hi,

I wanna know if there´s any possibility to have a mix row, between Inputs and Formulas, without the conditions "skip" or "in".

I want a dynamic formula to skip all years but real, and keep it automatic trough the years
Carlos (vzla)
Quantrix Forum Member
 
Posts: 11
Joined: Wed Aug 14, 2013 8:52 am

Re: Dynamic Skip?

Postby jlin » Fri Aug 18, 2017 2:02 am

Any update to this question? I know it's four years old but it's a worthwhile question. I find myself constantly wishing Quantrix had this capability too. Being able to manually override calculated values would make my models so much more user-friendly (since people are used to doing just that in Excel - e.g. as each month's Actuals are known, one just manually types in the Actuals in place of the formula that was previously calculating the Budget values).
jlin
Quantrix Forum Member
 
Posts: 51
Joined: Mon Jan 27, 2014 10:16 am

Re: Dynamic Skip?

Postby S a u » Fri Aug 18, 2017 9:14 am

jlin,

If I correctly understood your problem, then look how I would solve it. To do this, nothing needs to change in QM.
You do not have the required permissions to view the files attached to this post.
Best regards,
SAU
S a u
Quantrix Forum Member
 
Posts: 49
Joined: Fri Jun 20, 2014 8:57 am

Re: Dynamic Skip?

Postby jlin » Mon Mar 26, 2018 11:28 pm

Sau,
I just realized I never responded. In fact, I am already using your technique heavily, e.g. for every Category where I want to mix calculated and manual-input values, I have to add a "Metrics" category. (In my case, I name the categories like Category_A and Category_A_Inputs, for clarity).

This creates additional complexity in the model. Not every category in the model will need Budget vs Actuals; in a large model, how will the modeler keep track of which values need to be referenced just with "Category_A", and which need to be referenced with "Category_A:User Input"? And for end-users who are used to being able to override a calculated value directly in the cell (like Excel), it's a little harder for users to understand.

What I would like is for QM to support expressions in Skip (for instance with the syntax "Skip Where [expression]"), which would skip all Items where [expression] evaluates to true. Then, I could do the following:
  1. Create a descriptor in Category_A called "IsOverride". For each Item, if IsOverride is blank, then item value will adopt the calculated value will take effect. Else the item cell will remain an input cell.
  2. In the formula where I calculate the "default" calculated values, I use
    Code: Select all
    ... Skip where isempty(@descriptor(Category_A, IsOverride))

This feature would be like SQL "UPDATE XXX WHERE YYY, ZZZ". I could dynamically make certain items calculated or take manual input. And I wouldn't need to add extra categories to help with Input.
jlin
Quantrix Forum Member
 
Posts: 51
Joined: Mon Jan 27, 2014 10:16 am

Re: Dynamic Skip?

Postby S a u » Tue Mar 27, 2018 3:55 am

jlin,

Try another model structure. This model version is more complicated in logic, but it does not expensive in terms of the number of tracking cells.
You do not have the required permissions to view the files attached to this post.
Best regards,
SAU
S a u
Quantrix Forum Member
 
Posts: 49
Joined: Fri Jun 20, 2014 8:57 am

Re: Dynamic Skip?

Postby jlin » Sat Mar 31, 2018 10:42 am

Sau,
Yes, I see this way cuts down on the number of unneeded cells; depending on needs this would be a better solution. However, I still think Quantrix should add support for expressions in Skips :) . It gives you so much more flexibility and elegance, without need for more helper Categories or lookup matrices!

Still, thank you very much for your time!
jlin
Quantrix Forum Member
 
Posts: 51
Joined: Mon Jan 27, 2014 10:16 am


Return to Formulas and Functions

Who is online

Users browsing this forum: No registered users and 1 guest