Last 6 months data

[Anonymous]

Dear All

I have a requirement where I need to calculate average of last 6 months sales.

Example:  If we are in December we need to calculate average from June to November..

In Jan,  from July to Dec and so on.    Kindly help to achieve this possibility

Thanks & Regards

Chintan Gala

[vinieme12]

Sorry try

and vEndYearMonth = Date(Addmonths( Date($(vStartYearMonth)),-6),'YYYYMM')

[bobbydave]

I had the same issue only a few weeks back with an application i was working on

In your script

LOAD Date(Date#([TheYear Month]), 'YYYY-MM'), 'YYYY-MM') as [The Year Month]

Your expression

Avg({$<[The Year Month] = {">=$(=Date(AddMonths(Max(Today()),-6), 'YYYY-MM'))<=$(=Date(AddMonths(Max(Today()),-1), 'YYYY-MM'))"}>} [Your Value to Measure])

The expression will always calcalate the date between the last 6 months and last month always so for this month, you will get Dec last month and 6 months previous. Next Month, you will get Jan and 6 months previous. Make sure to declare [The Year Month] in your load script.

[luizcdepaula]

Hi David,

What worked for me was a little different but very similar. The main thing that I changed was to replace the [The Year Month] dimension to the actual [Date] dimension.

Added to load script:

     LOAD Date([Date]), 'YYYY-MM'), as [The Year Month]

Calculation expression in app:

     Avg({$<[The Year Month]={">$(=Date(AddMonths(Today(),-6),'YYYY-MM'))<=$(=Date(AddMonths(Today(),-1),'YYYY-MM'))"}>}[Your Value to Measure])

It is working and calculating previous 6 months.

Thanks,

LD

[bobbydave]

Hi Luiz,

Delighted it worked. Took me ages to get that working also.

Please mark as correct.

[Anonymous]

Hello Luiz

As you are using today()-6 ,  I guess it will go to previous 6 months from today. This would not work if we select any previous month as filter.

Thanks & Regards

Chintan Gala

[luizcdepaula]

Hello Chintan,

You would have to add an IF condition then.

Ex:

If( (GetSelectedCount (MonthYear) = 0 and GetSelectedCount (Year) = 0),

Avg({$<[The Year Month] = {">=$(=Date(AddMonths(Today(),-6), 'YYYY-MM'))<$(=Date(AddMonths(Today(),0), 'YYYY-MM'))"}>} [Your Value to Measure]),

Avg([Your Value to Measure]))

This expression will give you either last 6 finished full months if no date as to MonthYear or Year is selected, or the AVG of whatever months you select.

Or

If( (GetSelectedCount (MonthYear) = 0 and GetSelectedCount (Year) = 0),

Avg({ $ <[The Year Month] = {">=$(=Date(AddMonths(Today(),-6), 'YYYY-MM'))<$(=Date(AddMonths(Today(),0), 'YYYY-MM'))"}>} [Your Value to Measure]),

Avg({ 1 <[The Year Month] = {">=$(=Date(AddMonths(Max(Date),-6), 'YYYY-MM'))<$(=Date(AddMonths(Max(Date),0), 'YYYY-MM'))"}>} [Your Value to Measure]))

This expression will give you either last 6 finished full months if no date as to MonthYear or Year is selected, or the AVG of last 6 months from the MonthYear you select. Make sure you use 1 instead of $ on the second part of the expression.

Cheers,

LD

[Anonymous]

Dear Vineeth

I am unable to create the previous 6 months from

vEndYearMonth = Date(Addmonths( Date($(vStartYearMonth)).,-6),'YYYYMM')

Is there any other way around?

Regards

Chintan

[vinieme12]

Date(Addmonths( Date($(vStartYearMonth),'YYYYMM'),-6),'YYYYMM')

There was a full stop before comma  .,

[neelamsaroha157]

Try this

Avg({$<Date={"<$(=MonthEnd(AddMonths(Max(Date),-1))>$(=MonthStart(AddMonths(MonthEnd(AddMonths(Max(Date),-1)), -6)))"}>}Value)

[Anonymous]

It shows expression OK,  but later shows error as ')'  expected.