Two month comparison

[#1]

Good morning all,

I have a formula, contain a variable (vMaxDate).

In this instance it will show me November 2013 info.

How can I show information for October 2013 next to the column that shows the November 2013 info.

I want to create another variable (vMaxDate2) that will basically show the vMaxDate less one month.

I am not sure about the syntax of the formula.

Please advice the best way I can structure my formula.

Thanks

*R*

[tresesco]

Is your expression for current month working fine? If so, please post your expression here, it would be easier to modify your expression for second last month. Otherwise, share your sample app.

[#3]

Hi there,

Yes my current month is working fine.

This is one of the expression formulas for the current month:

count({<[Created Date] =,Month=,Year=>} if([Created Date] <= vMaxDate,if(Status='L',[Agreement Number])))

Thanks

*R*

[#4]

try this set analysis

sum({<DateField={'>=$(Vrolling2)<=$(vMaxDate)'}>}sales)

Vrolling2: =Date(MonthStart(vMaxDate,-1),'DD/MM/YYYY')

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

[#5]

Dear Rentia,

You can using set analysis for this case. Please try this formula:

count({$<Month=,Year=,[Created Date]={">=$(=AddMonths( max([Created Date]),-2 )) <=$(=AddMonths( max([Created Date]),0 ))"}>}if(Status='L',[Agreemenr Number]))

Regards,

Renyta

[tresesco]

Guessing your vMaxDate is holding the created date one month earlier. If so, you can try:

=count({<[Created Date] =,Month=,Year=, [Created Date]={"<=$(vMaxDate2) >$(vMaxDate)"},Status={'L'}>}[Agreement Number])

Where, vMaxDate2=AddMonths($(vMaxDate), -1)

[CELAMBARASAN]

Try this

=Count({<[Created Date] =,Month=,Year=, [Created Date]={"<=$(vMaxDate2)"},Status={'L'}>}[Agreement Number])

Where vMaxDate2 should have =AddMonths(vMaxDate, -1)

Hope it helps

[#8]

count({<[Created Date]={'>=$(Vrolling2)<=$(vMaxDate)'},status={'L'}>}[Agreement Number])

Vrolling2: =Date(MonthStart(vMaxDate,-1),'DD/MM/YYYY')

vMaxDate: =Date(max([Created Date]),'DD/MM/YYYY')

[#9]

Hi there,

Thank you. This one works for me.

Can you maybe help me with the following formula as well?

sum({<[Created Date]=,Month=,Year=>}if([Created Date] <= vMaxDate2,if(Status='L' and ([Product Type]>'5' or [Product Type]>='A'),NBV)))

Exactly the same scenario…want to show the info for October 2013.

I can just replace the count with sum etc… but I am not quite sure how to type the last bit stating that the product type should be bigger that 5  and bigger that type A.

Thanks

*R*