Skip to main content
Solved

Trending financial report


Forum|alt.badge.img

How do I create the column set to have a column for each month and a total for the year?  I want to be able to select the year and then have the columns for each month.   

Best answer by Laura02

Hi,

Notice the Visible Formula. you will edit the numbers in the formula for each month: 

Month 4:   =iif((@EndPeriod>='04' and @StartPeriod<='05'),True,False)

Month 5: =iif((@EndPeriod>='05' and @StartPeriod<='06'),True,False)

and so on.

 

View original
Did this topic help you find an answer to your question?

11 replies

dcomerford
Captain I
Forum|alt.badge.img+15
  • Captain I
  • 584 replies
  • August 22, 2023

This should do it. Create each column and then use your data Source to pick your periods for each column

Then on your Total use the 

This was a monthly PL and i did for a customer


Laura02
Captain II
Forum|alt.badge.img+19
  • Captain II
  • 3135 replies
  • August 22, 2023

Hello,

You will need 12 columns that specify Period Number, followed by a Total or a YTD Balance column. This column design is a P & L with Period balances (net change) for months 01 - 12 followed by Year to Date balance for period 12.

We can’t choose ‘just a year’; in this case we print for periods 01 to 12 or we can print for period 12 and see the same results.

 


Forum|alt.badge.img
  • Author
  • Jr Varsity III
  • 16 replies
  • August 22, 2023

This worked, other than - if I run the report as of April I was hoping that only Jan-Apr columns would be filled in and then the total for 2023 would be the first 4 months of the year but all data shows for all months.  Thoughts?  I have a 2022 column too so if I put in April it shows April 2022 YTD. 

 

Thank you!


Laura02
Captain II
Forum|alt.badge.img+19
  • Captain II
  • 3135 replies
  • Answer
  • August 22, 2023

Hi,

Notice the Visible Formula. you will edit the numbers in the formula for each month: 

Month 4:   =iif((@EndPeriod>='04' and @StartPeriod<='05'),True,False)

Month 5: =iif((@EndPeriod>='05' and @StartPeriod<='06'),True,False)

and so on.

 


Forum|alt.badge.img
  • Author
  • Jr Varsity III
  • 16 replies
  • August 23, 2023

This helped! Thank you! 


Forum|alt.badge.img
  • Author
  • Jr Varsity III
  • 16 replies
  • August 23, 2023

One more thing - my YTD column is still adding all columns (even those not visible).  How to adjust the formula to only include columns visible? 


Laura02
Captain II
Forum|alt.badge.img+19
  • Captain II
  • 3135 replies
  • August 23, 2023

Hello,

A YTD column will show YTD no matter what periods are visible.

Perhaps use 12 different YTD columns (Year to date with period 01, year to date with period 02, etc) with Visible formula on each one like this for period 05:  iif((@EndPeriod='05'

Laura


Forum|alt.badge.img
  • Author
  • Jr Varsity III
  • 16 replies
  • August 23, 2023

I had to do a calc column for each month and then was able to get it to work.


  • Freshman I
  • 7 replies
  • May 30, 2024

Hi @srengdahl,

Can you please elaborate on “I had to do a calc column for each month and then was able to get it to work.”

I am trying to get the total of YTD column only for the periods that are visible and not entire period from Jan to Dec.

Regards,

Raj Jasani


Laura02
Captain II
Forum|alt.badge.img+19
  • Captain II
  • 3135 replies
  • May 30, 2024

Hello,

I think SRengdahl used the suggestion from the previous post:

Laura02 wrote:

Hello,

A YTD column will show YTD no matter what periods are visible.

Perhaps use 12 different YTD columns (Year to date with period 01, year to date with period 02, etc) with Visible formula on each one like this for period 05:  iif((@EndPeriod='05'

Laura

 


Forum|alt.badge.img
  • Author
  • Jr Varsity III
  • 16 replies
  • May 30, 2024

Yes, that is correct.  I had to enter 12 different YTD columns and then it worked.  


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings