CALC MONTH TO DATE (MTD) FROM YEAR TO DATE (YTD)

[#1]

I have the data  cumulative as Year to date and the Date format is ('YYYYMM'). I am trying to calculate the Month To Date (MTD) for each month .  It sounds easy to just subtract the selected month from the previous month. But how can I accomplish this in QV  Set Analysis dynamically.

Any help is appreciated

[teiswamsler]

Hi Rehan

This set analysis works for me.

MTD

= Sum( {<
CalenderYear,
CalenderMonth,
CalenderQuater,
%OrderDate = {">=$(=num(MonthStart(Max(%OrderDate))))<=$(=Max(%OrderDate))"}
>} Sales )

YTD

Sum( {<
CalenderYear,
CalenderMonth,
CalenderQuater,
%OrderDate = {">=$(=Floor( Yearstart(Max(%OrderDate)) ))<=$(=Floor( Max(%OrderDate) ))"}
>} Sales )

This will dynamically change by the selection in calender.

/Teis

[sasiparupudi1]

try like this

Sum( {<DATE1 = {">=$(=date(MonthStart(Max(DATE1)),'YYYYMM'))<=$(=Max(DATE1))"}>} Sales)

hth

Sasi

[sasiparupudi1]

or

Sum( {<DATE1 = {"=$(=Max(DATE1))"}>} COUNTER1 )

[#5]

My data is already cumulative YTD, I need to back into it to get the MTD.

[#6]

My data is already cumulative YTD, I need to back into it to get the MTD.

[maxgro]

If you have a chart with a year month field, maybe with sum(field) - above(sum(field))

Or you can do it in the laod script.

[#8]

How can I do it in Load Script?. I would rather do that

[maxgro]

maybe this?

SET DateFormat='DD/MM/YYYY';

// start test data

x:

load

  addmonths(MakeDate(2014),rowno()-1) as ym,          // year month

  2*rowno()*rowno()+rowno() as ytd                                   // ytd value

AutoGenerate 24;

left join                                                                               // just to test, add another dimension

load * inline [

dim4

a

b

];

// end test data

// load ordered by .... and when one dimension or year chenge reset

// calc diff between row and previuos row ytd value

y:

NoConcatenate load

  dim4,

  ym,

  ytd,

if(dim4<>Peek(dim4) or year(ym)<>year(peek(ym)), ytd, ytd-Peek(ytd)) as mtd

Resident x

order by dim4, ym;

DROP Table x;

[#10]

YRMTH	YR	MTH	Sales (YTD)
201507	2015	Jul	20
201507	2015	Jul	30
201506	2015	Jun	40
201505	2015	May	55
201506	2015	Jun	65
201501	2015	Jan	75
201501	2015	Jan	80
201504	2015	Apr	90
201507	2015	Jul	95
Sales Data is Cumulative

I am trying to calculate Month to Date from this Year to Date data