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;

[Anil_Babu_Samineni]

You can use YearStart() in set analysis

[suryaa30]

No I don't understand. Can you please elaborate?

[Anil_Babu_Samineni]

This is my intention

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

[suryaa30]

The Idea is right now I have

Dimension: Year

Expression: Count(Policy)

For YTD chart I need same as above a single expression chart

Dimension: Year

Expression: Count({Set expression} Policy)

Entire data set Max date 27-Sep

the set expression should restrict data from starting of any year till 27-Sep of that year

[Anil_Babu_Samineni]

For YTD chart I need same as above a single expression chart

Dimension: Year

Expression: Count({Set expression} Policy)

Entire data set Max date 27-Sep

the set expression should restrict data from starting of any year till 27-Sep of that year

Yes, This is my answer

Count({<DateField = {">=$(=Date(YearStart(Max(DateField)))) <=$(=Max(DateField))"}>} Policy)

[suryaa30]

Just tell me something

what should be the format for Date Field in your expression?

[Anil_Babu_Samineni]

Try from your end. My format is DD-MM-YYYY

Count({<DateField = {">=$(=Date(Date#(YearStart(Max(DateField)),'YourFormat'),'DateFieldFormat')) <=$(=Max(DateField))"}>} Policy)

[suryaa30]

stalwar1‌ loveisfail‌ kush141087‌

Need your help.

Hi In attached App we have sheets A and B

Both Sheets have Monthly and YTD charts.

In sheet A  YTD chart I have used 3 expression to achieve my need which is For each year the data should be between start of that year to Max date determined.

I want this to be done in a single expression so that as the number of years increase I dont have to write a separate expression for each year.

I need a set expression similar to this

max data in entire data model. 2-Jun

Dim: Year and Month

Exp:Sum({for all data between start of the year and 2-Jun of that year}Revenue)

In YTD the values for each month should be accumulative.

Actual:

2011

Jan-10

Feb-20

Mar-30

YTD:

2011

Jan-10

Feb-30(10+20)

Mar-40((10+20+30)

[sunny_talwar]

May be try this

Sum({<[Trade Date] = {"=SetDateYear([Trade Date], Max(TOTAL Year)) <= Max(TOTAL [Trade Date])"}>} [Trade Revenue])