Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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..
hello
which formula do you use to calculate your sum ?
do you use a calendar ?
=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')))))) .........
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
Are you talking about MTD or YTD?
YTD
Hi Prachi,
Can you share sample app and expected result?
Thanks!!
Please check and let me know.i can't share sample app.
Hi Prachi,
PFA.
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