Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
What is your date field format? What object are you doing this in? Does it have Year, MonthYear as dimension?
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.
if you select April, you need data from dec17 to Mar 18? Do you have Month Year field too in your model?
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.
So you mean to say, basis on your selection, result should be of previous month till Dec 17?
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.
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)