Calculate YTD for Month using Date

[pooja821]

Hi,

I need to calculate YTD for month using Date field.

For example for month of October-17, i need to calculate YTD for October, i.e. all month from Oct-16 to Oct-17 upon selection of Oct date from date filter.

Kindly help on this.

[pradosh_thakur]

check this document out. Hope this helps.

YTQ, QTD, MTD and WTD

[pooja821]

I can fetch the answer from the above document.

They have converted the date field into numbers first using floor function.

I want to implement it using date field only.

Kindly help on this.

[sunny_talwar]

What is your date field format? What object are you doing this in? Does it have Year, MonthYear as dimension?

[pooja821]

My Date is in DD-MMM-YYYY format.

i  need  to calculate this YTD for a specific month which is basically a  previous month of selected date from date filter.

I m showing this in a graph (as below ) which basically have a month as dimension fetched from date only.

i need to calculate YTD for Dec-17 which basically is 4th previous month of Apr-18 as shown above in the graph.

I just have a date field.

Kindly help on this.

[shiveshsingh]

if you select April, you need data from dec17 to Mar 18? Do you have Month Year field too in your model?

[pooja821]

No,

I need a YTD for Dec month i.e

Since the recent month is coming out be April-18 and all other previous months are reflecting accordingly.

what i want is that for Dec-17 month, i need to calculate YTD for this month i.e for Dec YTD the month should come out to be from April-17 to Dec-17 as our financial year starts from April of every year.

[shiveshsingh]

So you mean to say, basis on your selection, result should be of previous month till Dec 17?

[pooja821]

Hi Sunny,

I have calculated YTD for month and stored in variable called $(Month_YTD) using below expression:

Concat(distinct {1<fiscal_year={"$(=$(year))"},

accounting_period={"$(='>=' & 1 & '<=' & $(v_accounting_period))"}>}Date,',')

Where $(year) is as concat(distinct {$<Date={"$(=$(prev_month4))"}>}fiscal_year,',')

and $(v_accounting_period) is as  concat(distinct {$<Date={"$(=$(prev_month4))"}>} accounting_period,',')

The value of this Month_YTD variable is coming out to be 30-Nov-2017,31-Dec-2017 which is correct.

But when i m using this variable $(Month_YTD) in the below expression i.e.

count({$<Date={"$(=$(Month_YTD))"}>} distinct emp_id) , i am not getting the answers.

Although when i m manually feeding the dates in place of this Variable i.e

count({$<Date={'30-Nov-2017','31-Dec-2017'}>} distinct emp_id)  ,then i m getting the answer .

Is there something wrong with this expression.

Kindly help on this.

[sunny_talwar]

May be try this

Change Month_YTD to this

Chr(39) & Concat(DISTINCT {1<fiscal_year = {"$(=$(year))"}, accounting_period = {"$(='>=' & 1 & '<=' & $(v_accounting_period))"}>} Date, Chr(39) & ',' & Chr(39)) & Chr(39)

and then the main expression to this

Count({$<Date = {$(=$(Month_YTD))}>} DISTINCT emp_id)

or

Count({$<Date = {$(Month_YTD)}>} DISTINCT emp_id)