MTD & LTD in set analysis

[#1]

Hi All,

Happy new year to everyone!!!!!

I have req. as below and I need some help in this.

I have certain fields like Product,number,values,id,date,cal_month fields.

I need to calculate the MTD and LTD formulae’s with respect selection and without selection in front end level.

1. If I am selecting the particular month it has to show the values with that particular month like values upto that month(Live Till Date) and values(MTD i.e only that particular month) in one chart
2. 2.If am not selecting any list box it has to values LTD(till the date) and MTD(only current month) in one chart.

Plz find the attached sample data and I am unable to attach the original data.

Thanks in advance,

Ravi

[nirav_bhimani]

Try this,

LTD:
= sum(
{<
Revenue_Date= {">=$(=Date(YearStart(min({1}Revenue_Date))))<=$(=max(Revenue_Date))" }, Revenue_Week= , Revenue_Quarter=, Revenue_Month= >}
GrossRevenue)

MTD:

= sum(
{<
Revenue_Date= {">=$(=Date(MonthStart(Max(Revenue_Date))))<=$(=max(Revenue_Date))" }, Revenue_Week= ,Revenue_Year=, Revenue_Quarter= >}
GrossRevenue)

Regards,

Nirav Bhimani

[iktrayanov]

Use pivot table with two expressions

MTD:

sum(Value)

LTD:

rangesum(above(sum(Value),0,RowNo()))

See Attached