Need to get last month value in the total

[#1]

Hi,

I need to show the maximum month value, i.e., if i select 2013 and jun month, then i need to show the values from jan to jun and my total column should show as jun month value.

if i select mar month, i have to show jan to mar and total column shoud be Mar month value.

Hope it will be clear.

thanks in advance.

Helen

[sunny_talwar]

May be this:

If(Dimensionality() = 1, Last(Aggr(Sum(Value), cat, Temp_Month)), Sum(Value))

If(SecondaryDimensionality() = 0, Sum({<Temp_Month = {"$(=MaxString(Temp_Month))"}>}Value), Sum(Value))

[rubenmarin]

Hi Helen, you can try with:

if(SecondaryDimensionality()>0,

RangeSum(Before(Sum(Value), 0, Max(TOTAL Temp_Month))),

sum({<Temp_Month={"$(=TextBetween('$(MonthNames)', ';', ';', Max(Temp_Month)-1))"}>} Value)

)

[#4]

Tried but not working ruben..

[sunny_talwar]

Are you looking to get accumulated values for each month? or are you trying to see all months before the month you have selected but with their own value? Can you try the attached

[antoniotiman]

If You want last Month (Jun,Jul or May) try

If(SecondaryDimensionality() = 0,
FirstSortedValue(DISTINCT Aggr(Sum(Value),cat,Temp_Month),-Temp_Month),
sum(Value)
)

[rubenmarin]

Ok, I didn't tested past years, try with:

if(SecondaryDimensionality()>0,

RangeSum(Before(Sum(Value), 0, Max(TOTAL Temp_Month))),

sum({<Temp_Month={"$(=SubField('$(MonthNames)', ';', Max(Temp_Month)))"}>} Value)

)

[#8]

Helen,

I believe you are trying to show YTD data

You can try this

YTD - Year To Date

A date should be selected and it will look for the Starting date of the year to the selected date.

Ex: date selected is 21-03-2014 then YTD is 01-01-2014 to 21-03-2014

Expression would be

Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num(YearStart(Max(DateNum))))<=$(=Max(DateNum))"}>} Sales)

or

you can try this

=Sum({<Month={$(=max(Month))}, Date={'>=$(=MonthStart(Max(Date)))<=$(=Date(Max(Date)))'}>} [Sales])

--

Muffaddal

How to get previous Month data

[neelamsaroha157]

Check this out