Calculate MTD Position using seperate daily values

[#1]

Hi,

I am trying to create an expression(s) to calculate MTD values and compare them to the previous month.

My data is based on daily positions and not rolled up positions, i.e. 01 Jan 2013 will have its own value and 02 Jan 2013 will also have its own value and so on.

I have tried a few expressions, but they don't correctly give me what I want to see. For example

For current month

Sum

({<C_Month={'$(=Month(max(C_date)))'}, C_date = {'$(=max(C_date))'} >}Value)

This gives me the value for the latest day only, while I actually want the sum of everything to the latest latest day i.e. 1st of any month to the latest day available. Once I get this I can then create a similar expression and compare it to the same time in the previous month i.e. My overall aim is to compare for example from 01/06/2013 – 15/06/2013 to 01/05/2013 to 15/05/2013

Any help to get this working as intended will be greatly appreciated.

Many thanks

[jonathandienst]

Hi

If the expression provided works for the latest day, then I think this should work for MTD for the latest month:

Sum({<C_Month={'$(=Month(max(C_date)))'}, C_date =>}Value)

And this should work for the previous month

Sum({<C_Month={'$(=Month(max(C_date), -1))'}, C_date =>}Value)

Regards

Jonathan

[#3]

Hi Jonathan,

Thanks for your reply.

Your suggestion really only works for the current month. If a user makes a selection and wants to see what happened in July against June, it shows the full month values, rather than the intra month values that I want.

Ideally I would like to see a comparison for 1st June to 15th June and 1st July to 15th July and so on, which will basically be a sum of the values recorded for all those days.

Regards

[Oleg_Troyansky]

If you need to apply the same MTD logic to prior months, then you need to formulate the search condition that will apply separately to month(C_date) and Day(C_Date). Something long the following lines (can't guarantee the exact syntax, just a direction):

Sum({< C_Date={"=(num(C_Month)=num($(=Month(max(C_date)))) and  (day(C_Date) <= day(max(C_Date)))"}, C_date =>} Value)

So, in a nutshell, you are selecting dates that have a month that's equal to the highest month and a date that's less or equal to the highest available date. Tweak it to your specific needs.

best,

Oleg Troyansky

www.masterssummit.com

[#5]

Try This,

C_date={'>=$(VstartMonth)<=$(vMaxDate)'}

Create 2 Variable please find below tha values of the variables................

VstartMonth=Date(MonthStart(vMaxDate),'DD/MM/YYYY')

vMaxDate==Date(max(C_date),'DD/MM/YYYY')

[#6 · Accepted solution]

Hi ,

Find the attached Application which has both MTD & Previous MTD expressions.

[#7]

Have you try this or not,

Try This,

C_date={'>=$(VstartMonth)<=$(vMaxDate)'}

Create 2 Variable please find below tha values of the variables................

VstartMonth=Date(MonthStart(vMaxDate),'DD/MM/YYYY')

vMaxDate==Date(max(C_date),'DD/MM/YYYY')

[#8]

Thanks Bhaskar, that was exactly what I needed.

Regards