5 Replies Latest reply: Feb 19, 2016 2:22 AM by jagan mohan rao appala

# Last day sale of the month

Dear All,

I have expression like below

(SUM({\$<FinancialYear=,MonthName=,FiscalYear={\$(=max(FiscalYear)-1)},Quarter=

>}[Gross Sales Amount])

e.g when I select 2015-16 then its showing

Jan 16 Jan 15

20 cr    17 cr

But actually jan 15 sale is 19 cr

when I select 2014-15 then its showing

Jan 15 Jan 14

19 cr    18 cr

That is right.

In this it is not pick last day of sale.

Can anyone give some light on this

• ###### Re: Last day sale of the month

HI,

Deal this in script with a flag then you won't this issue

Transaction:

*,

MonthName(DateFieldName) AS MonthName

FROM DataSource;

MaxDateInMonth:

MonthName,

Max(MonthName) AS DateFieldName,

1 AS MaxDateInMonthFlag

RESIDENT Transaction

GROUP BY MonthName;

Now use below expression

(SUM({\$<FinancialYear=,MonthName=,FiscalYear={\$(=max(FiscalYear)-1)},Quarter=

,[Posting Date]=, MaxDateInMonthFlag={1}

>}[Gross Sales Amount])

Regards,

Jagan.

• ###### Re: Last day sale of the month

Hi Jagan,

Thanks for quick response.

I have achieve the same using MonthEnd function in the YTD expression but am not able to for MTD.

YTD script:

Sum({\$<FinancialYear=,FiscalYear={\$(=max(FiscalYear)-1)},MonthName =

>}[Gross Sales Amount])

Do you have any other solution on this.

• ###### Re: Last day sale of the month

Try this

Sum({\$<FinancialYear=,FiscalYear={\$(=max(FiscalYear)-1)},MonthName =

>}[Gross Sales Amount])

• ###### Re: Last day sale of the month

I have also tried this but unfortunately its taking wrong figure but for YTD is showing me right figure.

• ###### Re: Last day sale of the month

Try implementing using Flags it is easier and also you will have better performance.

Regards,

Jagan.