Daily data compare in end of month

[hamdaniahmad]

Hello All,

I have daily comparing data in any object, but I have some problem in my analysis in the end of month.

basically my analysis is daily analysis (compare date to date), but in the end of month I wanna compare

total this month vs total last month. I use this formula for daily analysis :

=sum({<Date_Fix={">=$(=MonthStart(max({<DESC= {'SALES'}>}Date_Fix)))<=$(=max({<DESC= {'SALES'}>}Date_Fix))"},DESC= {'SALES'},YEAR,MONTH,DAY>}sales_qty).

The problem when use that formula, when this month (February) max date is 28, but last month (January) max date is 31.

analysis will be 28 Feb vs 28 Jan. can I change the formula when reaching end of month become 28 Feb vs 31 Jan.

Does anybody know the tricky?

Thank you

[Thiago_Justen_]

What about this:

sum({<Date_Fix={">=$(=MonthStart(AddMonths(max({<DESC= {'SALES'}>}Date_Fix),-1)))<=$(=AddMonths (max({<DESC= {'SALES'}>}Date_Fix),-1))"},DESC= {'SALES'},YEAR,MONTH,DAY>}sales_qty)

Cheers

[hamdaniahmad]

Hello Thiago,

Your formula is worked to find value in last month right?

my issue is,

how we find total end of month VS end of month for comparing achievement in the end of month.

Example :

=sum({<Date_Fix={">=$(=MonthStart(max({<DESC= {'SALES'}>}Date_Fix)))<=$(=max({<DESC= {'SALES'}>}Date_Fix))"},DESC= {'SALES'},YEAR,MONTH,DAY>}sales_qty)

/

sum({<Date_Fix={">=$(=MonthStart(AddMonths(max({<DESC= {'SALES'}>}Date_Fix),-1)))<=$(=AddMonths (max({<DESC= {'SALES'}>}Date_Fix),-1))"},DESC= {'SALES'},YEAR,MONTH,DAY>}sales_qty)

above formula result is : total 1-28 Feb 18 / total 1-28 Jan 18.

All I want is to find result :

total 1-28 Feb 18 / total 1-31 Jan 18.

But also can see result by daily, for example max date is 20, and the formula become :

total 1-20 Feb 18 / total 1-20 Jan 18

Thank you

[Thiago_Justen_]

What if we use this:

=sum({<Date_Fix={">=$(=MonthStart(max({<DESC= {'SALES'}>}Date_Fix)))<=$(=max({<DESC= {'SALES'}>}Date_Fix))"},DESC= {'SALES'},YEAR,MONTH,DAY>}sales_qty)

/

sum({<Date_Fix={">=$(=MonthStart(AddMonths(max({<DESC= {'SALES'}>}Date_Fix),-1)))<=$(=MonthEnd (AddMonths (max({<DESC= {'SALES'}>}Date_Fix),-1)))"},DESC= {'SALES'},YEAR,MONTH,DAY>}sales_qty)

[Thiago_Justen_ · Accepted solution]

Based on your second condition I guess this will be helpful:

If (Max (Date_Fix)=MonthEnd (Max (Date_Fix)),

sum({<Date_Fix={">=$(=MonthStart(max({<DESC= {'SALES'}>}Date_Fix)))<=$(=max({<DESC= {'SALES'}>}Date_Fix))"},DESC= {'SALES'},YEAR,MONTH,DAY>}sales_qty)

/

sum({<Date_Fix={">=$(=MonthStart(AddMonths(max({<DESC= {'SALES'}>}Date_Fix),-1)))<=$(=MonthEnd (AddMonths (max({<DESC= {'SALES'}>}Date_Fix),-1)))"},DESC= {'SALES'},YEAR,MONTH,DAY>}sales_qty)

,

sum({<Date_Fix={">=$(=MonthStart(max({<DESC= {'SALES'}>}Date_Fix)))<=$(=max({<DESC= {'SALES'}>}Date_Fix))"},DESC= {'SALES'},YEAR,MONTH,DAY>}sales_qty)

/

sum({<Date_Fix={">=$(=MonthStart (AddMonths(max({<DESC= {'SALES'}>}Date_Fix),-1)))<=$(=(AddMonths (max({<DESC= {'SALES'}>}Date_Fix),-1))"},DESC= {'SALES'},YEAR,MONTH,DAY>}sales_qty)

)

[hamdaniahmad]

Hello Thiago,

Thank you for your sugggestion.

with a little modification (underline), I get the logic. and here is my formula :

If (Day(Max({<DESC= {'SALES'}>}Date_Fix))=Day(MonthEnd(Max({<DESC= {'SALES'}>}Date_Fix))),

(sum({<Date_Fix={">=$(=MonthStart(max({<DESC= {'SALES'}>}Date_Fix)))<=$(=max({<DESC= {'STU'}>}Date_Fix))"},DESC= {'STU'},YEAR,MONTH,DAY>}sales_qty)

/

sum({<Date_Fix={">=$(=MonthStart(AddMonths(max({<DESC= {'SALES'}>}Date_Fix),-1)))<=$(=MonthEnd (AddMonths (max({<DESC= {'SALES'}>}Date_Fix),-1)))"},DESC= {'SALES'},YEAR,MONTH,DAY>}sales_qty))

,

(sum({<Date_Fix={">=$(=MonthStart(max({<DESC= {'SALES'}>}Date_Fix)))<=$(=max({<DESC= {'SALES'}>}Date_Fix))"},DESC= {'SALES'},YEAR,MONTH,DAY>}sales_qty)

/

sum({<Date_Fix={">=$(=MonthStart(AddMonths(max({<DESC= {'STU'}>}Date_Fix),-1)))<=$(=AddMonths(max({<DESC= {'SALES'}>}Date_Fix),-1))"},DESC= {'SALES'},YEAR,MONTH,DAY>}sales_qty))

)

Regard.

[Thiago_Justen_]

Sound's great man.

Cheers