Announcements
cancel
Showing results for
Did you mean:
Creator II

## YTD calculations

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;

1 Solution

Accepted Solutions
MVP

Use this if you have QV12 or above

22 Replies

You can use YearStart() in set analysis

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
Creator II
Author

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

This is my intention

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
Creator II
Author

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

 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

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
Creator II
Author

Just tell me something

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

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

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
Creator II
Author

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)

MVP

May be try this