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..
can you please share a screenshot. i am not able to view this file.
Try this,
Backend --
Load *,
Date#(Capitalize(Month)&' '&Year,'MMM YYYY') as Year_Month;
Load * Inline [
Year , Month , Count
2018,JAN,10
2018,FEB,15
2018,MAR,30
2018,APR,5
2018,MAY,10
2018,JUN,25
2017,JAN,20
2017,FEB,11
2017,MAR,20
2017,APR,15
2017,MAY,6
2017,JUN,5 ];
SET vFiscalYearStartMonth = 4;
LET vStartDate = 42461;
LET vEndDate = 43282;
Let vToday1 = num(Today()-1);
CYCalendar:
Load
*,
Month(Test_date) AS Month_Cal,
YearName(Test_date, 0, $(vFiscalYearStartMonth)) AS FiscalYear,
Num(Test_date) as C_ACCOUNTING_DATE_NUM,
date(Test_date,'MMM YYYY') as Year_Month
;
LOAD
Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1) AS Test_date ,
RangeSum(Peek('RowNum'), 1) AS RowNum
AutoGenerate vEndDate - vStartDate + 1;
in frontend-
use this expre-
Sum({<Test_date = {">=$(=Yearstart(max(Test_date),0,4))<=$(=max(Test_date))"},Month=>}Count)
Take filters - FiscalYear & Month
Hi Prachi,
for your Current year YTD write formula as below:
Sum({<Date={">=$(=YearStart(Max(DateFiled),0,4))<=$(=Max(DateFiled))"}>} Value)
Thanks,
Arvind Patil