Calculation of YTD using only the month field as it is the only thing available.

[Brownie]

Hey there,

i need to calculate the YTD of  a certain field. and the only thing they have is Month. 

i need to calculate budget. they had given months separately which i using a cross table created a month field. But now how do i calculate a ytd in my front end using set analysis??? 

[KGalloway]

Does your month include the year value too? If so, something like the following may work:

If not, could you give a small sample data set that matches the one you are using?

[Brownie]

here's the QVD for sample data. i need YTD for budget

[KGalloway]

Without a more detailed date field, your options are very limited. I was able to create the two tables shown below.

The first shows each region, the month, the total amount in that month and region, and the running sum over months for each region (the sum resets for each region).

The second table shows each month, the total amount in that month (of all regions together), and the running sum over the months.

The two running sum expressions are 

rangesum(above(sum(if(Region = Region, Budget_Revenue)), 0, 12)) for the first table

rangesum(above(aggr(sum(Budget_Revenue), Month), 0, 12)) for the second table

Notably, I mapped the month values to numbers so that I could sort them properly.

[vinieme12]

you can create a datefield using Year and Month using the makedate() function

makedate(Yearfield,Monthfield,1) as MonthStartDate

then use this date field in set analysis for YTD,MTD,QTD calculatations

YTD

=sum({<MonthStartDate={">=$(=YearStart(Max(MonthStartDate)))<=$(=Date(Max(MonthStartDate)))"}>}Something)

[Brownie]

Below given is the script i've been using to find YTD . meaning if a particular month is selected as in march is selected it should show ytd from jan till march. 

but the problem is that the date field  isn't in the budget Qvd.  The picture of data model is given below.

if(month(max((Date)))='Jan',sum({< MonthB={'Jan'}>}Budget_Revenue),
if(month(max((Date)))='Feb',sum({< MonthB={'Jan','Feb'}>}Budget_Revenue),
if(month(max((Date)))='Mar',sum({< MonthB={'Jan','Feb','Mar'}>}Budget_Revenue),
if(month(max((Date)))='Apr',sum({< MonthB={'Jan','Feb','Mar','Apr'}>}Budget_Revenue),
if(month(max((Date)))='May',sum({< MonthB={'Jan','Feb','Mar','Apr','May'}>}Budget_Revenue),
if(month(max((Date)))='Jun',sum({< MonthB={'Jan','Feb','Mar','Apr','May','Jun'}>}Budget_Revenue),
if(month(max((Date)))='Jul',sum({< MonthB={'Jan','Feb','Mar','Apr','May','Jun','Jul'}>}Budget_Revenue),
if(month(max((Date)))='Aug',sum({< MonthB={'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug'}>}Budget_Revenue),
if(month(max((Date)))='Sep',sum({< MonthB={'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep'}>}Budget_Revenue),
if(month(max((Date)))='Oct',sum({< MonthB={'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct'}>}Budget_Revenue),
if(month(max((Date)))='Nov',sum({< MonthB={'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov'}>}Budget_Revenue),
if(month(max((Date)))='Dec',sum({< MonthB={'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'}>}Budget_Revenue),))))))))))))

This picture might help you understand the situation.  I need to optimize this long statement into a single short one or something. 

I hope this helps you understand better

[KGalloway]

This might be a solution:

sum({<Month=>} if(Month <= num(keepchar(right(getfieldselections(Month, ',', 12), 2), '1234567890')), Budget_Revenue))

I used this calculation in a KPI object. It shows 0 except when I select values in Month. Then it will show the sum of Budget_Revenue in all regions in all months <= to the max chosen Month value. There may be a better way to find the max chosen month value using aggr, but I couldn't get it to work with just Region, Month, and the budget fields.