Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a line chart where ill be having month as dimension
I need to show 24 months from aug back to 24 months
Measure is YTD
any idea?
May be this? and you can use which field you want degrade.
Sum({<DateField = {">=$(=AddYears(Max(Month),-2)) <=$(=Max(Month))"}>}Sales)
Hi Gowtham,
May be like this:
Sum({<Date= {">=$(=Addmonths(Max(Month),-24)) <=$(=Max(Month))"}>}Value)
Thanks,
Arvind Patil
this will select the date but wat abt ytd in this formula
You can simply define the YearStart() in your first operator
Sum({<DateField = {">=$(=YearStart(AddYears(Max(Month),-2))) <=$(=Max(Month))"}>}Sales)
Or
Sum({<DateField = {">=$(=YearStart(AddYears(Max(DateField),-2))) <=$(=Max(DateField))"}>}Sales)
If I use this it will take year start I don't want like that ur first expression looks good it fetches date from aug 15 to july 17 but I need to sum the data lik for aug 15 I need to sum data from april 15-august15
same like for all the months
If second expression doesn't help then why do you call name it as YTD? Do you know the definition of YTD? And you says, you need last 24 months? But here for Aug 15 it is indicating to me 5 months data only when select on month as Aug 15. I am not sure, I followed you this moment
Hi anil thanks for your reply
I have attached a data please check
in that ill be having sum in the second column and third column is having ytd
if you see the third column till apr 15 the value will be fine but after apr 15 its summing up with the old data
but my output should be like if its apr 15 it shud show sum(Leaver)and may shud sum up apr 15 and may 15 and so on .Please check the file I have attached.
my third column formula is
=IF(Period1=YearStart(Period1,0,4),Sum(VOLUNTARY_LEAVER),RangeSum(Above(Sum(VOLUNTARY_LEAVER),0,12)))
If you are using straight table use Full Accumulation radio button to achieve.
If it is in pivot table then use like below
Rangesum(
Below(
Sum({<DateField = {">=$(=YearStart(AddYears(Max(Month),-2))) <=$(=Max(Month))"}>}Sales)
,0),
Above(Sales,1,RowNo(TOTAL)))