Month To Date Customised

[#1]

Hello!

I want to create a MonthToDate report that can show me the turnover like that:

day 1   day 2  .............. day 31  monthtodate(the sum of the other days) ....etc

How can i calculate the turnover for each day of the month and not lower the program's functionality with all the calculus implied? I have to mention that i have other objects on the sheet, and the formulas will be recalculated with every selection i make.

Thanks!

[techvarun]

see this

it may help

[#3]

I'm not sure you understood what i need. "day 1", "day 2",  etc each represents a column. Each column has its own expression area and i can't use a general formula for them. Which generates another question: can i dynamically create all those columns based on a general formula?

[orital81]

Use a Pivot Table, set the date to be horizontal.

Your expression will be Month To Date value.

This way, you will get a column dynamically created for each day.

[#5]

yes use pivot tables..it works

[#6]

Yes, you are wight, it can work with a pivot. Now i have another problem. The first column will contain the days of the current month. By clicking the "+" in the day cell, all the information will refresh. The thing is that i can't import only the days i need, even though i wrote the formula in the Enable Condition field (Dimensions->Settings for Selected Dimension->Enable Condition). All i want is that the days are automatically be added, as time goes by. How can i make things right?

[orital81]

Hi Laura

Please clarify: Which are the days you need and what condition you wrote to enable it?

It will be easier to help if you could attach your example, or at least screen shots.

[#8]

For the MTD for September, i want to have in the first column all the days, from the beginning of the month, up until now-11 September and i want the program to add dynamically each day in the chart. The condition i wrote is

=IF(AN=YEAR(TODAY()) AND LUNA=MONTH(TODAY()))

I want my table to have the structure as in the file i attached: date, group of fields, monthtodate, last year's monthtodate, difference, difference in % and a smiley face in the last column.

[orital81]

In order to show this MTD compared to last year MTD you can use this:

First, set variables:

LET vThisYear = Year(Today());

LET vLastYear =Year(Today())-1;

LET vThisMonth = Month(Today());

LET vLastMonth = Month(MonthStart(Today())-1);

LET vDayToday = Day(Today());

Make sure you have a calendar with Year, Month and Day

Now add calculations:

This MTD =

    sum({$<Year = {$(vThisYear)},Month = {'$(vThisMonth)'}, DayDate = {"<=$(vDayToday)"}>} Total_Value)

Last MTD =

    sum({$<Year = {$(vLastYear)},Month = {'$(vThisMonth)'}, DayDate = {"<=$(vDayToday)"}>} Total_Value)

Now, you don't need to add any condition in the dimension.

Let me know if that helped.

Ori

[#10]

So you suggest tu use these formulas in the first column's expression (the one with the dates of the month)?