12 Replies Latest reply: Dec 29, 2015 6:11 AM by Vidhya Vk

# Issue with YTD value

Hello everyone,

I face the following problem in my report.

Current Year (2016) is the Financial Year (2015-2016)

I need to do Current Year vs Last Year YTD Comparison. I am getting
correct numbers in LY YTD column, but when I click on the Year field, number
disappears and I get only hyphen.

Request anyone to help me out.

Thanks,

vidhya

• ###### Re: Issue with YTD value

This means you need apply calender and check with financial year is available in your calender.

Vikas

• ###### Re: Issue with YTD value

Hi,

You need to exclude the selections on date fields then you will get the values

Example:

Sum({<Year=, Month=, Quarter=, Date={'Some Condition'}>})

Check this link for some sample expressions.

Set Analysis for certain Point in Time

• ###### Re: Issue with YTD value

Jagan have shared the solution with his posted article which easily explained you set analysis for these YTD, WTD, MTD etc calculations which is always useful.

You can go through this as well:

YTQ, QTD, MTD and WTD

• ###### Re: Issue with YTD value

Hi,

You have to create a calendar whereby you store all the dates in one table and than link that to a key in the table you want to use. Could be a fact that has the corresponding dates

• ###### Re: Issue with YTD value

Hi,

I have used Master Calendar in my data model.

My Year is Financial Year 15-16, 14-15, 13-14 etc.

Now, I have to comparison for 15-16 vs 14-15.

When no year is selected, I get the numbers properly in Previous year YTD column,

but when I select  15-16 from the list box, PY column gives only hyphen (rather it should give numbers for 14-15).

When I select both 15-16 and 14-15, I get the right numbers.

Is there a way to tackle this problem?

Vidhya

• ###### Re: Issue with YTD value

Hi,

Regards

• ###### Re: Issue with YTD value

Hi,

sum({<Date={">=\$(vLYMinDate)<=\$(vLYMaxDate)"}>}Exit_Count)/

(((sum({<Date={"\$(vLYMinDate)"}>}OpeningBalance)+ sum({<Date="\$(vLYMaxDate)"}>}ClosingBalance))/2))

Basically I am calculating the Attrition YTD%.

\$(vLYMinDate)= min({<FIN_YEAR_NO={"\$(vPrevYear)"}>}Date)

\$(vLYMaxDate)= max({<FIN_YEAR_NO={"\$(vPrevYear)"}>}Date)

\$(vPrevYear)=max(FIN_YEAR_NO)-1

FIN_YEAR_NO will have values like 2016, 2015, 2014, etc.

Thanks,

Vidhya

• ###### Re: Issue with YTD value

Hi Vidhya,

If you do not have any other filters except the Fiscal Year then you can wite the exp as below:

sum({1<Date={">=\$(vLYMinDate)<=\$(vLYMaxDate)"}>}Exit_Count)/

(((sum({1<Date={"\$(vLYMinDate)"}>}OpeningBalance)+ sum({1<Date="\$(vLYMaxDate)"}>}ClosingBalance))/2))

OR if you have othe filters also. the you can write it as :

um({1<Date={">=\$(vLYMinDate)<=\$(vLYMaxDate)"},[Financial Year]=>}Exit_Count)/

(((sum({1<Date={"\$(vLYMinDate)"},[Financial Year]=>}OpeningBalance)+sum({1<Date="\$(vLYMaxDate)"},[Financial Year]=>}ClosingBalance))/2))

Thanks and Regards,

Ankita

• ###### Re: Issue with YTD value

Hi Ankita,

I tried it.. It doesn't work!! Because I am applying filter on dates also in the set. so, even if I freeze, Year field, no change is seen.

Thanks,

Vidhya

• ###### Re: Issue with YTD value

Hi Vidhya,

Can you post a sample application. That would be much helpful.

Thanks and Regards,

Ankita

• ###### Re: Issue with YTD value

try this:

sum({<Date=,Date={">=\$(=vLYMinDate)<=\$(=vLYMaxDate)"}>}Exit_Count)

/

(sum({<Date=,Date={"=\$(=vLYMinDate)"}>}OpeningBalance)+ sum({<Date=,Date={"=\$(=vLYMaxDate)"}>}ClosingBalance))/2

• ###### Re: Issue with YTD value

Nope. it doesn't work