Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
This means you need apply calender and check with financial year is available in your calender.
Vikas
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.
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
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:
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?
Thanks in advance,
Vidhya
Hi,
what is your expression??
can you post your expression??
Regards
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
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
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