set analysis

[#1]

Hi Experts,

I need to do an expression which shows difference of current month value & last 3 months average value.

I did the expression as

(Sum({< MonthYear =,Date= {">=$(=date(MonthStart(vMaxSalesDate)))<=$(=date(vMaxSalesDate))"}>}sales)

/

Sum({<MonthYear =,Date= {">=$(=date(MonthStart(vMaxSalesDate)))<=$(=date(vMaxSalesDate))"}>}Workingdays))

-

Avg(aggr(

(Sum({<MonthYear =,Date= {'>=$(=Date(MONTHSTART(AddMonths(vMaxSalesDate, -3))))<=$(=Date(MONTHEND(AddMonths(vMaxSalesDate, -1))))'} >} sales)

/

Sum({<MonthYear =,Date= {'>=$(=Date(MONTHSTART(AddMonths(vMaxSalesDate, -3))))<=$(=Date(MONTHEND(AddMonths(vMaxSalesDate, -1))))'} >} Workingdays))

,Branch))

Is the expression wrong? if so, Please help me with the correct expression.

[jonathandienst]

It depends on how vMaxSalesDate is defined and the format of the dates.

[MK_QSL]

You mean to say...

Current Month Sales - Average of Last Three Months Sales?

[ajsjoshua]

Hi,

Try this

(Sum({< MonthYear =,Date= {">=$(=date(MonthStart(vMaxSalesDate)))<=$(=date(vMaxSalesDate))"}>}sales)

/

Sum({<MonthYear =,Date= {">=$(=date(MonthStart(vMaxSalesDate)))<=$(=date(vMaxSalesDate))"}>}Workingdays))

-

Rangeavg(aggr(

(Sum({<MonthYear =,Date= {'>=$(=Date(MONTHSTART(AddMonths(vMaxSalesDate, -3))))<=$(=Date(MONTHEND(AddMonths(vMaxSalesDate, -1))))'} >} sales)

/

Sum({<MonthYear =,Date= {'>=$(=Date(MONTHSTART(AddMonths(vMaxSalesDate, -3))))<=$(=Date(MONTHEND(AddMonths(vMaxSalesDate, -1))))'} >} Workingdays))

,Branch))

[jonathandienst]

What do you get if you add a text box with the expression =vMaxSalesDate?

The expression will show the daily average of the last month compared to the daily average of the previous 3 months. Is that what you require?

[ahammadshafi]

Or is it

Average sales of current month - average sales of last three months?

[#7]

I have given the variable definition as

vMaxSalesDate = Max(Date)

[#8]

yes.

Current month values - Avg(last 3 months value)

[#9]

No. its not working

[MK_QSL]

If you say Current Month Sales, Do you mean by MTD or Avg Sales per Day for this month?

Because if you consider MTD, what if the current date is 2nd... You only have 2 days sales and final result would be negative..