Hello,

I'm trying to create a Year-Over-Year report for Year-To-Date data. The .Y data item correctly pulls in the the current YTD data but there does not seem to be a corresponding data item for the previous year's cummulative YTD data. I would have expected to see a .LY data item.

Am I correct in concluding that I can't do this or am I just missing something obvious? Is there some work around? Is there a way to define new data types?

Thank you.

Richard

Hi Richard,

Are you using Adagio Ledger or FX? And if Ledger, is it version 90A or an earlier version?

If Ledger 90A, use the code {n}Y which would be the YTD changes for n years ago. Thus {1}Y would be YTD last year, {2}Y would be YTD 2 years ago, and so on.

If you are using Adagio FX or an earlier version of Ledger, LYY is the YTD last year code. LY gives the full previous year (not YTD).

If you are not sure what particular field results will give, a sample specification GL!SuperSpec90 (or AF!SuperSpec for Adagio FX) shows a list of all available fields and their outputted values and fiscal date ranges, based on an account and fiscal period you select. This sample form is a useful tool.

Hi Richard,

".LY" exists in both products to return the entire prior year number. ".LYY" returns Last Year YTD figures.

Thanks very much. .LYY is what I was looking for. I did not discover it before because I was only looking in the data types under "Prv. Yr. Net Change In Balance". Didn't occur to me to look in "Relative Change In Balance".

Thanks very much.

Richard

I have a report which contains lines with all zero values that refuse to go away even though I have zero suppression turned on.

I have a test line that I use to observe the behavior and have verified that I know how to toggle the behavior successfully.

I have an account group containing a single range line referencing a single account. A cell G33 in that line contains "F33/F$50" which returns a zero value because F33 contains zero.

Cell G34 lies in the Total line, contains "=SUM(G33)", and, of course, returns a zero value.

In my test line, if I enter the text "=SUM(G33)" (contents of cell G34) into a cell, I get the proper zero suppression behavior. But! If I copy the contents cell C34 ("=SUM(G33)") and paste it into my test line, zero suppression ceases to work.

If I copy the contents of cell G33 ("F33/F$50") zero suppression appears to work properly.

So, there appears to be something fishy going on in the case that I copy the contents of a cell that resides in a Total line.

This is actually a big issue because I frequently create new reports by copying and pasting entire columns. Doing this one time essentially turns off zero suppression for the entire document which I then have to laboriously hand edit.

Is this a know bug? Is there a workaround or am I just doing something wrong.

Thanks again.

Richard

Hello Richard,

If you highlight the total line and right mouse button on the Row number, what is the setting of the Zero suppress option on the pop-up menu?

Also, if you actually print (or print preview) the statement, is the zero suppression working (sometimes the design view shows rows that will be suppressed when the statement is printed).

Hi Andrew,

Thanks again. I did not realize that the setting could be overridden at the cell level. Zero suppression was off for the cell that was not working. So, I selected my entire sheet, right clicked on the mouse and set zero suppression. I think it is working now.

Sorry, I made this request as a reply when I intended a new post and then re-submitted as a new post thinking you would not see the reply.

Thanks again for your prompt response.

Richard

Andrew,

I've got a hand entered cash flow line in my report that sums the net income number (Cell D368) with a depreciation number (Cell D339) contained in a generated line. Trouble is, when I collapse and re-expand, adagio resets the sum to add D368 + D350 which is meaningless.

I tried D$339 but had no better luck.

Can you tell me what is going on? I'm still struggling a bit with understanding the behavior of collapsed vs. expanded mode. I'd also like to "hide when printed" certain generated lines but adagio does not appear to remember the setting across a cycle of collapsing and expanding.

Thanks much.

Richard

Generally, you want to be able to be able to prevent a total row from being suppressed, even if the total is zero. You have control of this on a row-by-row basis.

Remember, with the financial reporter, "if you need it, it is there" (although it might be tough to find!).

Just ask here and we'll point you in the right direction. You might also learn a few tips and tricks by reviewing the recorded webinar on the financial reporter.

Hi Richard,

When you expand a SmartSheet, this creates rows based on the formulas and formatting of the original row that has .Range in column A.

If you collapse a SmartSheet, this deletes all of the generated rows, not just hiding the rows.

Because of this, you don't want to be making adjustments to cells in generated rows, because as soon as you refresh (or collapsed and re-expand), those changes will be gone. Or basing formulas on cell references to generated cells, because it will get changed the next time you collapse the sheet.

It will be tricky to attempt to add the amount of a specific cell that is found in a generated row with another cell that is not generated. This has to do with cell-referencing and that the depreciation amount is not guaranteed to always be in the same row (depending on zero-suppressed rows or if you add new accounts). You would be better off having the depreciation account in a separate row of the report, not getting generated and thus not subject to the whims of an unexpected cell-reference or being deleted.

To hide particular Generated rows, you cannot use the 'Hide when Printed' format, but you could potentially hide the rows. This would involve using the 'RowFormat' function to conditionally hide a generated row, based on criteria you can define in an IF formula. This is done by putting .RowFormat in the top-right cell, and a formula such as the one's below in the the right-most column, in a row with .Range in column A.

=IF($B10 = "1000" , "FormatHidden" , "")
=IF($D336 >= 5000 , "FormatHidden" , "")