Formula Variable

[Former Member]

Hi Guys,

I need to create formula variable on the Infoobject for date. I need to calculate no of days elapsed (i.e. Difference between Sales order date and invoice date).

In order to achive it during query run time I need to create formula variable for both dates. Not sure how do we ccreate one.

Can anyone help please?

Thanks

Kirk

I promise to return points for all useful answers as I always do.

[Former Member · Accepted solution]

Bhanu,

Thanks for the quick reply., Yours was very helpful answer. Will award the points.

Well I have one more issue would sure you would be of some help on this.

Once I calculate no of days elapsed. I need to count all the Sales Orders where no. of days elapsed are more than 40 days.

Can I do it without creating Virtual Key Figure.

I mean by restricting couting with the help of no of says calecualted in formula. or may be using IF ELSE.

Please advise.

Thanks

Kirk

[Former Member · Accepted solution]

Hi Kirk,

When you have determined days elapsed with formula variable you can use a formula with boolean operator "Greater Than" 40. You will obatain a column with a counter for every value bigger than 40.

Ciao.

Riccardo.

[Former Member · Accepted solution]

Thanks Recardo for the quick reply. Will sure award you points for this.

Now I need to go a step further.

I need to check if Invoice Date is blank (i.e. invoice not raised) then calculate the number of days as system date - SO Creation date and see if its less than 40 days. If it is count these orders as well.

Can it be included in the same formula.

Thanks

Kirk

[Former Member · Accepted solution]

Hi Kirk,

For the last roblem you can use the same tool of the revious adding a a last formula: you can use formula Data Functions DELTA or COUNT that return 1 or 0 if the object is equal or not to '0'. You have to apply this formula to the Formula Variable based on Invoice Date. The rusult has to be multilied to the difference of System Date and SO Creation Date.

Ciao.

Riccardo.

[Former Member]

Hi Guys,

Small issue in here. No. of days being calculated using formula variable only works when the date are in the report drilldown. if I remove them from drilldown, no of days arenot being calculated.

Any work around on it.

Thansk

Kirk

[Former Member]

don't know if you ever figured this out, but in order to aggregate after the formula is run on each record, it has to be a global calculated key figure. Local calculated key figures aggregate before the calculation is determined

[former_member188975]

Hi Kirk,

You need to start creating the formula variable in the formula box of the query designer. This variable will be of type replacement path and you can specify the date InfoObject (as value to be replaced).

Hope this helps...