Calculating Month to Date Sales Value

[#1]

Dear All,

We are in FMGC Company and we are receiving sales data from distributor in daily basis.

Now i need to calculate MTD sales, when i select 12th December i need 12 days sum or when i select  5th Dec 5 i need 5 days sales.

I have applied this formula but it's not giving correct value.

=Sum({$<Month = {$(=Max(Month))},

YearFiscal = ,

Quarter = ,

Month = >} ValuesinMT)

Highly appreciated if any one could help me.

Thanks in advance

Raju

[#2]

Hi Raju

Is Month your selected field or do you need to select Day ?

=Sum({$<Day= {=$(=Max(Day))},

YearFiscal = ,

Quarter = ,

Month = } ValuesinMT)

chris

[#3]

Try this

sum({<Date_field= {"<=$(=Date_Field)>=$(=MonthStart(Date_Field))"}

YearFiscal = ,

Quarter = ,

Month =

> } ValuesinMT)

Regards,

Kabilan K

[#4]

Thank you so much christian....

I have applied this formula but it's not giving correct value. say for example if select Dec 5, it's giving  only 5th day sales in all month.

Rgds,

Raju

[#5]

Thank you so much kabilan...

I have tried the way what you are suggested. It's giving only current date sales.

Is there any other way to move forward?

Rgds.

Raju

[#6 · Accepted solution]

Try this

sum({1<Date_field= {"<=$(=Date_Field)>=$(=MonthStart(Date_Field))"}

YearFiscal = ,

Quarter = ,

Month =

> } ValuesinMT)

[tresesco]

Could you please post your sample app?

[jagan]

Hi Raju,

Try like this if you have a date field

=Sum({$<Month =, YearFiscal = , Quarter = , DateDimension={'>=$(=MonthStart(Max(DateDimension)))<=$(=Date(Max(DateDimension)))'} >} ValuesinMT)

Note : If you have any other date related then ignore selection by give DimensionName=.

If you are not getting values check the date format in DateDimension and MonthStart(Max(DateDimension)) are same if not format the date by using Date().

Hope this helps you.

Regards,

Jagan.

[#9]

Thank you so much kabilan, It working perfectly .....

Great Help. Much appreciated.

Thanks & Regards,

Raju

[#10]

Thank you so much Jagan.

Your coding is working perfectly. Great Help, Much appreciated.

Thanks & Regards,

Raju