Privious Year YTD

[Anonymous]

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?

[sibin_jacob]

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

[Anonymous]

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?

[sibin_jacob]

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)

[yenumulagopi]

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

[Anonymous]

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?

[Anonymous]

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

[thannila]

Try this below code:

sum({<Year={$(vPriorYear)},{<Datekey={">=$(=YearStart(AddYears(max(Datekey),-1)))<=$(=AddYears(max(Datekey),-1))"},

Month=,Quarter=,WeekDay=,MonthlyWeekNumber=>}accessories_revenue)

@

[PrashantSangle]

Try below

Sum({<Year={$(=max(Year)-1)},fiscal_quarter=, Month=, [Date]=,Month={"<=$(=max(Month))"}>}accessories_revenue)

Regards,

[Anonymous · Accepted solution]

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)