7 Replies Latest reply: Jun 13, 2016 6:37 AM by Umamaheswara Reddy

# Help me to get only last 3 months data dynamically

HHi all

i I have 2015 (Oct,Nov,Dec) and 2016(Jan,Feb) data, so I need to display only last 3 months data (Dec 2015, Jan 2016, Feb 2016).

I tried with set analysis by using below mentioned expression

Sum({1<Month={">=\$(=MaxString(Month) -2) <=\$(=MaxString(Month))"}>Sales)

but the above condition fetch the data only 2015 only.

HHelp me to solve

THanks

AR UMAMAHESH

• ###### Re: Help me to get only last 3 months data dynamically

Hi,

try this,

=sum({1<Date={'>=\$(=(Num(MonthStart(Max(DateField), -2))))<=\$(=(Num(MonthEnd(Max(DateField)))))'}>}Sales)

-Joyson G

• ###### Re: Help me to get only last 3 months data dynamically

Hi all

i I have 2015 (Oct,Nov,Dec) and 2016(Jan,Feb) data, so I need to display only last 3 months data (Dec 2015, Jan 2016, Feb 2016).

I tried with set analysis by using below mentioned expression

Sum({1<Month={">=\$(=MaxString(Month) -2) <=\$(=MaxString(Month))"}>Sales)

but the above condition fetch the data 2015 data only.

If I use max(year) it will fetch only max year data. Ie 2016

Help me to solve this

THanks

AR UMAMAHESH

• ###### Re: Help me to get only last 3 months data dynamically

Use this expression

sum({<Date={">=\$(=addmonths(Max(Date), -2)) <=\$(=Max(Date))"}>} Data)

• ###### Re: Help me to get only last 3 months data dynamically

Try this attachment,

• ###### Re: Help me to get only last 3 months data dynamically

Hi,

Sometimes there may be requirements from users where they want to see the charts in from certain point of time like YTD, QTD, MTD, Last 5 Years etc., please find the set analysis expressions for this type of scenarios.

YTD Sales (Year To Date)

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=\$(=YearStart(Today()))<=\$(=Today())’}>} Sales )

Note: Year=, Quarter=, Month=, Week= excludes the selections in Year, Quarter, Month and Week dimensions.

QTD Sales (Quarter To Date)

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=\$(=QuarterStart(Today()))<=\$(=Today())’}>} Sales)

MTD Sales (Month To Date)

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=\$(=MonthStart(Today()))<=\$(=Today())’}>} Sales)

WTD Sales (Week To Date)

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=\$(=WeekStart(Today()))<=\$(=Today())’}>} Sales)

Last 5 Years Sales

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=\$(=YearStart(Today(), -4))<=\$(=Today())’}>} Sales )

Last 6 Quarters Sales

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=\$(=QuarterStart(Today(), -5))<=\$(=Today())’}>} Sales )

Last 12 Months Sales

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=\$(=MonthStart(Today(), -11))<=\$(=Today())’}>} Sales )

Last 15 Weeks Sales

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=\$(=WeekStart(Today(), -14))<=\$(=Today())’}>} Sales )

Last 10 Days Sales

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=\$(=Date(Today()-9))<=\$(=Today())’}>} Sales )

Yesterday Sales

Sum({<Year=, Quarter=, Month=, Week=, Date={‘\$(=Date(Today()-1))’}>} Sales )

Hope this helps you

Vikas

• ###### Re: Help me to get only last 3 months data dynamically

Thanks Vikas,

I followed the above scenarios, finally I got the result

THanks,

AR UMAMAHESH

• ###### Re: Help me to get only last 3 months data dynamically

HI

The below is the dynamic way to get last 3 months data

First we need to create a variable to get MaxDate from the data then use that variable in the set analysis as mentioned below.

sum({<as_of_dt={">=\$(=MaxDate,-3))  <=\$(=MaxDate)"}>}sales)

THanks,

AR UMAMAHESH