Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
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])
I have also tried this but unfortunately its taking wrong figure but for YTD is showing me right figure.
Try implementing using Flags it is easier and also you will have better performance.
Regards,
Jagan.