YTD for fiscal year

[Anonymous]

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..

[Anonymous]

can you please share a screenshot. i am not able to view this file.

[PunamWagh]

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

[arvind_patil]

Hi Prachi,

for your  Current year YTD write formula as below:

Sum({<Date={">=$(=YearStart(Max(DateFiled),0,4))<=$(=Max(DateFiled))"}>} Value)

Thanks,

Arvind Patil