Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I am struggling alot to show the Previous year YTD info in a table.
Below mentioned is my expression. Its showing the entire previous year data. But logically it has to show till August 2016.
Sum({<Year={$(vPriorYear)}, fiscal_quarter=, Month=, [Date]=, Datekey={">=$(=Num(YearStart(AddYears(Max(Datekey),-1)))) <=$(=Num(AddYears(Max(Datekey),-1)))"}>}accessories_revenue)
Can you please help me to fix this issue?
I used the below expression...Its working as per my expectation .... tanq so much for all your suggestions
=Sum({<segment=,Year={$(vPriorYear)}, fiscal_quarter=, Month=,Date={"$(='>='&Date(YearStart(Max(Date),-1),'MM/DD/YYYY') & '<=' & Date(AddYears(Max(Date),-1),'MM/DD/YYYY'))"}>}accessories_revenue)
Create a flag for Previous Year and current year
Then use that flag in the expression
if(Month(Date_cloumn)>8,year(Date_column)&' to '&year(Date_column)+1,
if(Month(Date_cloumn)<8,year(Date_column)-1&' to '&year(Date_column),
if(Month(Date_cloumn)=8 and Date(Date_column,'DD')<=25,
year(Date_column)-1&' to '&year(Date_column),
year(Date_column)&' to '&year(Date_column)+1
))) as Fiscal_Year
Hi Sibin
Tan so much for your reply...
I already have a previous year and current year flag....
Let vlastyear=YearStart(Today(),-1);
Let vcurrentyear=YearStart(Today());
But nothing is working out
Do you have any straight forward formula which gives Prior year YTD output?
Try this
Create Month Column, values should be 1,2,3 etc.
if it already available, you can use the existing column or you can create using
Date(Date_Column,'MM') as Month_Column
Create variable vMonth_value
Definition give it as 8
Sum({<Year={$(vPriorYear)}, fiscal_quarter=, Month=, [Date]=,
Month_Column={"<=$(=vMonth_value)"},
Datekey={">=$(=Num(YearStart(AddYears(Max(Datekey),-1)))) <=$(=Num(AddYears(Max(Datekey),-1)))"}>}accessories_revenue)
Hi Surya,
Can you please try with the below expression
Sum({<Year={$(vPriorYear)}, fiscal_quarter=, Month=, [Date]=, Month={'<=$(=max({<Year={$(=max(Year))}>}accessories_revenue)
If it is not working, please let me know. In this expression instead of using the variable you can use
Year={$(=max(Year)-1)}.
Thanks
Gopi
Hi Gopi
Tanq for you reply...
I think there is some issue in the below part
Month={'<=$(=max({<Year={$(=max(Year))}>}
Can you please chk?
I have reformed my formula like this
Sum({<Year={$(=max(Year)-1)},fiscal_quarter=, Month=, [Date]=, Month={'<=$(=max({<Year={$(=max(Year))}>}))>'}>}accessories_revenue)
Its giving null output
Try this below code:
sum({<Year={$(vPriorYear)},{<Datekey={">=$(=YearStart(AddYears(max(Datekey),-1)))<=$(=AddYears(max(Datekey),-1))"},
Month=,Quarter=,WeekDay=,MonthlyWeekNumber=>}accessories_revenue)
@
Try below
Sum({<Year={$(=max(Year)-1)},fiscal_quarter=, Month=, [Date]=,Month={"<=$(=max(Month))"}>}accessories_revenue)
Regards,
I used the below expression...Its working as per my expectation .... tanq so much for all your suggestions
=Sum({<segment=,Year={$(vPriorYear)}, fiscal_quarter=, Month=,Date={"$(='>='&Date(YearStart(Max(Date),-1),'MM/DD/YYYY') & '<=' & Date(AddYears(Max(Date),-1),'MM/DD/YYYY'))"}>}accessories_revenue)