topic Re: Privious Year YTD in App Development

Privious Year YTD

Anonymous — Mon, 06 Aug 2018 05:00:40 GMT

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?

Re: Privious Year YTD

sibin_jacob — Mon, 06 Aug 2018 05:15:07 GMT

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

Re: Privious Year YTD

Anonymous — Mon, 06 Aug 2018 05:24:13 GMT

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?

Re: Privious Year YTD

sibin_jacob — Mon, 06 Aug 2018 05:33:48 GMT

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)

Re: Privious Year YTD

yenumulagopi — Mon, 06 Aug 2018 05:36:50 GMT

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

Re: Privious Year YTD

Anonymous — Mon, 06 Aug 2018 05:57:20 GMT

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?

Re: Privious Year YTD

Anonymous — Mon, 06 Aug 2018 06:00:20 GMT

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

Re: Privious Year YTD

thannila — Mon, 06 Aug 2018 06:13:28 GMT

Try this below code:

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

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

Re: Privious Year YTD

PrashantSangle — Mon, 06 Aug 2018 06:16:39 GMT

Try below

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

Regards,

Re: Privious Year YTD

Anonymous — Mon, 06 Aug 2018 06:53:15 GMT

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)

Re: Privious Year YTD

yenumulagopi — Mon, 06 Aug 2018 07:01:10 GMT

Try this.

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

Thanks