Last day sale of the month

[prma7799]

Dear All,

I have expression like below

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

,[Posting Date]={">=$(=MonthStart(max({$<FiscalYear={$(=max(FiscalYear))}>}[Posting Date]),-12))<=$(=addmonths(max({$<FiscalYear={$(=max(FiscalYear))}>}[Posting Date]),-12))"}

>}[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

[jagan]

HI,

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

Transaction:

LOAD

*,

MonthName(DateFieldName) AS MonthName

FROM DataSource;

MaxDateInMonth:

LOAD

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.

[prma7799]

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 =

,[Posting Date]={">=$(=YearStart(max({$<FiscalYear={$(=max(FiscalYear))}>}[Posting Date]),-1,4))<=$(=AddMonths(MonthEnd(max({$<FiscalYear={$(=max(FiscalYear))}>}[Posting Date])),-12))"}

>}[Gross Sales Amount])

Do you have any other solution on this.

[jagan]

Try this

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

,[Posting Date]={">=$(=YearStart(max({$<FiscalYear={$(=max(FiscalYear))}>}[Posting Date]),-1,4))<=$(=MonthEnd(AddMonths(max({$<FiscalYear={$(=max(FiscalYear))}>}[Posting Date])),-12))"}

>}[Gross Sales Amount])

[prma7799]

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

[jagan]

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

Regards,

Jagan.