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.

[#2]

yes. current month means MTD.

i need to provide data for the daily improvement.

even if it is negative, no problem

[sunny_talwar]

Have you checked if each of the component alone is working as you would expect? I see 4 expressions,  I would suggest to break them down into 4 expressions to check if one of them is not behaving or if all is not behaving. Once you have that, we can move forward from there.

Also try the below expression, I made some minor enhancements and deleted some stuff which was not really necessary:

(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(vMaxSalesDate, -3)))<=$(=Date(MonthEnd(vMaxSalesDate, -1)))"}>} sales)

/

Sum({<MonthYear =, Date = {">=$(=Date(MonthStart(vMaxSalesDate, -3)))<=$(=Date(MonthEnd(vMaxSalesDate, -1)))"}>} Workingdays), Branch)))

[jagan]

Hi,

Try this

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

/

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

-

(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))

I am not sure why you are using Aggr with Branch?

Regards,

Jagan.

[#5]

hi Jagan,

i tried this without Aggr.

but it is not giving average

[jagan]

Hi,

Can you attach sample file and your expected result for particular selections, so that it would be easier to understand and provide solution.

Regards,

Jagan.