MTD and YTD

[#1]

Anybody could please help me to find out the formulas for MTD and YTD, without selecting Year and Month.

I have done it with selecting Year and Month.

Thanks in Advance.

[Anonymous]

Hi Renjith,

The easiest way I think is to create a calender with a Year and a YearMonth field in your script.

Try This:

Load ...

Year(DATE) AS Year,

date(monthstart(DATE), 'YYYY-MM') AS YearMonth,

From ...

Then:

Sum ({$<Year={$(=Max(Year))},   DATE={'<=$(=Today())'} >} SALES)

Gives you the SUM(SALES) of the max(Year) form your data (up to today)

And

Sum ({$<YearMonth={$(=Max(YearMonth))},   DATE={'<=$(=Today())'} >} SALES)

Gives you the SUM(SALES) of the max(YearMonth) form your data  (up to today)

[kamalqlik]

Hi Renjith,

This can be done in many ways...

In case you want static result----

1)Use     inyeartodate(D, $(vToday), 0) * -1 AS CYTD  in back end(vToday=LET vToday = vMaxDate;).

ansd in expression in FrontEnd use-----
sum(if(CYTD, LineSalesAmount)).

For LYTD--
sum(if(FPYTD, LineSalesAmount))  where FPYTD=inyeartodate(D, $(vToday), -1) * -1 AS FPYTD in Script

For MTD---sum(if(CMTD, LineSalesAmount))

Where CMTD=inmonthtodate(D, $(vToday), 0) * -1 AS CMTD in script

For Previous month this year sum(if(PM, LineSalesAmount))......

where  PM =inmonth(D, $(vToday), -1) * -1 AS PM  in script

.

For Dyanamic result you have to go for the Selection....or you can go as Dennis have recommended but you have to bypass the Year and Month Field.

=num(sum({<[Fiscal Month]=, [Fiscal Quarter]=,[Fiscal Year]={$(=max([Fiscal Year]))},[ Date]={"<=$(vCurrDate)"}>}[Sales])) .

.

Hope it help.I have also bypass Quarter.

Regards

Kamal

[#4]

Dennis,

the first was working fine.

but the second (Sum ({$<YearMonth={$(=Max(YearMonth))},   DATE={'<=$(=Today())'} >} SALES))

returns a null value.

Please have a look on that.

[kamalqlik]

Hi ,

It is working wrong because max(yeaMonth) is returning the null value in your case.

This is because you must be using....

(Month(Date)&'-'& right(Year(Date),2)) as YearMonth for calculating YearMonth or some other method for calculating YearMonth.

This is text field and when you are using max(YearMonth) it will return null value.

So you have to change it to a number format.

So use Like this to make yearmonth

The field that you have made Yearmonth,

Make a resident table with all the field

and use

Date(Date(Date#(YearMonth,'MMM-YY'),'DD-MM-YYYY'),'MMM-YY') as YearMonth1,

resident your table.

To make the YearMonth Number.

and use this YearMonth1 in your expression.

Hope it helps.

Regard

KAmal