Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

YTD for fiscal year

Hello everyone,

I stuck on YTD calculation for fiscal year.

I have year from 2002 to 2019 and need to calculate YTD on fiscal year.

for current year(2018) its calculate from Month APR but for rest of the it calculate from Month JAN.

2018  JAN 10

2018  FEB 15

2018  MAR 30

2018  APR 5

2018  MAY 10

2018 JUN  25

WHEN I SELECT YEAR 18 AND MONTH APR THEN ITS SHOWS 5 & I F SELECT MAY THEN IT SHOWS 15

2017  JAN 20

2017  FEB 11

2017 MAR 20

2017 APR  15

2017 MAY 6

2017 JUN  5

WHEN I SELECT YEAR 17 AND MONTH APR THEN ITS SHOWS 66 & I F SELECT MAY THEN IT SHOWS 72 (It calculate from jan but i need to show from apr)


please help me to find out.


Thanks..


1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

12 Replies
olivierrobin
Specialist III
Specialist III

hello

which formula do you use to calculate your sum ?

do you use a calendar ?

Anonymous
Not applicable
Author

=SUM({<Region={'INDIA'}, FiscalYear={{$(=FYChart3)},Month={"<=$(=vMonthNo)"}>}Value)/1000

vMonth3.1 =If(Isnull(getfieldselection(Month,"",""), Max(month),(getfieldselection(Month,"","")

FYChart3= If(Isnull(getfieldselection(FiscalYear,"",""), Max(FiscalYear),(getfieldselection(FiscalYear,"","")

vMonthNo =IF(vMonth3.1 ='Jan',1,IF(vMonth3.1 ='Feb',2,IF(vMonth3.1 ='Nov',11,'12')))))) .........

Anonymous
Not applicable
Author

For selected year

=SUM({<Region={'INDIA'}, FiscalYear={{$(=FYChart3)},Month={"<=$(=vMonthNo)"}>}Value)/1000

vMonth3.1 =If(Isnull(getfieldselection(Month,"",""), Max(month),(getfieldselection(Month,"","")

FYChart3= If(Isnull(getfieldselection(FiscalYear,"",""), Max(FiscalYear),(getfieldselection(FiscalYear,"","")

vMonthNo =IF(vMonth3.1 ='Jan',1,IF(vMonth3.1 ='Feb',2,IF(vMonth3.1 ='Nov',11,'12')))))) .........


and  for last year


=SUM({<Region={'INDIA'}, FiscalYear={{$(=FYChart3-1)},Month={"<=$(=vMonthNo)"}>}Value)/1000

agastya
Creator
Creator

Are you talking about MTD or YTD?

Anonymous
Not applicable
Author

YTD

Anonymous
Not applicable
Author

Hi Prachi,

Can you share sample app and expected result?

Thanks!!

Anonymous
Not applicable
Author

Please check and let me know.i can't share sample app.

Data Issue.png

Anonymous
Not applicable
Author

Hi Prachi,

PFA.

MK9885
Master II
Master II

Your data in word document is a big pain to work on....

XL files is always easier....

anyway, if you have a Num Month field, as in Month numbers then create a flag in back end like below...

if(Num_Month>=4,1,0) as FYTD_Flag, <<< This may be a preceding load depending on how the data is loaded.

After having that flag in front end

You may need 4 different expressions

1. Sum({ <      Year={2018} >} Production) >> Selected Month

2. Sum({ <      Year={2017} >} Production)  >> Selected Month

3. Sum({ <    Month = {"<=$(=num(Month))"} ,  FYTD_Flag={1},Year={2018}  >} Production)  >>2018 YTD

3. Sum({ <    Month = {"<=$(=num(Month))"} ,  FYTD_Flag={1},Year={2017}  >} Production)  >>2017 YTD

Or provide a clean data in XL file to work on?

I tested it on my end and adding a flag will only start the Month from Apr, this will be static