YTD calculations

[suryaa30]

Hi All I am expecting a chart like below.

Dimension: Year

Expression: Count(GrossSales_Policy)

Now I want to create a YTD chart. Here YTD means the maximumum date in the maximum year.

so max year is 2017

max date is 27

max month is sep

in the chart i should see data for

1-jan-2016 to 27-Sep-2016

1-jan-2015 to 27-Sep-2015

Individually I have calculated them using below expressions. I want to have a expression to accomodate any number of year's data. so I need not add expression for each year.

if 2016 is selected then for CY=2016

PY=2015

PY_1=2014

eYTDMovementsCY

Count({<Year={'$(=Max(Year))'},Date3={"<=$(=$(vSelectYrDate))"},Month=>}GrossSales_Policy)

eYTDMovementsPY

Count({<Year={'$(=Max(Year)-1)'},Date3={"<=$(=$(vPrevYrDate))"},Month=>}GrossSales_Policy)

vMaxYear Year(Max(Date3))

vMaxPrYear Year(Max(Date3))-1

vAllMaxMonth Num(Month(Max( ALL Date3)))

vAllMaxDate Date(Max(ALL Date3), 'DD-MMM-YY')

vAllMaxDay Day(Max(ALL Date3))

vSelectYrDate Date(MakeDate($(vMaxYear),$(vAllMaxMonth),$(vAllMaxDay)), 'DD-MMM-YY')

vPrevYrDate Date(MakeDate($(vMaxPrYear),$(vAllMaxMonth),$(vAllMaxDay)), 'DD-MMM-YY')

master calendar used;

[suryaa30]

! In your Aggr expression what does this signify? (Month,(NUMERIC)))

because I have now sorted the Master Calendar while loading but the accumulation is not showing correctly

[sunny_talwar]

Sorting of the Month field within the Aggr() function

The sortable Aggr function is finally here!

[suryaa30]

Thanks Much