Set analysis calculation for every month

[#1]

Hello everyone,

I have a table like the following (I've reduced the calendar date to a single field; in my app it's an calendar table):

Project_ID	Calendar_Date	Order_Date	Amount
1	04.04.2012	20.05.2012	100
2	04.04.2012	13.12.2012	200
3	04.04.2012	06.09.2012	50
...	...	...	...
4	01.05.2012	09.05.2012	150
5	01.05.2012	01.01.2013	250
...	...	...	...

So, what I'm trying to achieve is a chart which calculates the sum of amount for every month within a specific range. Here, there are all values where the Order_Date is less than or equal to (Calendar_Date + 6 months). On base of this statement I have developed the following formula:

=Sum({$<Order_Date={"<=$(=AddMonths(MakeDate(Only(Year), Only(Month), 1),3))"}>} Amount)

Year and Month are fields from the calendar table. I know that a result will be shown only when a specific calendar date is selected because of the Only function. But I would have a chart where a result is calulcated for every calendar date respectively the unique month which is the dimenion of that chart.

Any ideas?

Regards,

Philipp

[#2]

Hi Philipp,

Is your field Calendar_Date really existing, if notI would create it and use it :

I would then transform this field into a numeric value and add 180 (days) <=> 6 months

I would write a formula like this :

=Sum({$<Order_Date={"<=$(=Num(Calendar_Date)+90))"}>} Amount)

You may,have also to create a numeric field of the Order_Date field :

=Sum({$<Order_Date_Num={"<=$(=Num(Calendar_Date)+90))"}>} Amount)

Regards

Kévin

[#3]

Yes, it exists. But in my application it's a key field (numeric field) to the calendar table which contains the calendar date. I have made the example table only to simplify my aim.

Also, adding a numeric value to the dates isnt really what I want. By the way, 180 days are unequal to 6 months.

Philipp

[#4]

Could you describe the key linking your calendar and your Order table.

If the key is a date with a numeric format I would test this :

Sum({$<Order_Date={"<=$(=AddMonths(Date(Key)),3))"}>} Amount)

I'm also not sure that you need to use the function Only, have you test without it?

About 180 days = 6 months, you have in average 30 days in a month, so 30*6 = 180, after it depends how accurate you want to be.

Kévin

[#5]

The calendar key is, as I said, a numeric field (see below). This Calendar_Key is in the Order table instead of Calendar_Date. But this doesnt matter.

Calendar_Key	Reference_Date
1	04.04.2012
2	01.05.2012
...	...

Yes, I've tried it without Only but didnt work either. No result will be calculated then.

I have to be very accurately with the days und months. So, the average or something like that is not a possible solution for me.

Philipp

[#6]

If you need to use the Only function, it seems that for 1 OrderDate, you have many CalendarDate.

You could replace Only by Max but for me it is not what you want.

[#7 · Accepted solution]

Hi,

I made it.

I reworked my data model in such way that the order table now contains additional fields for the time range and the amount. So, the "new" amount will be calculated only when it is in the range which you can specify in the script part. Overall, I've made this by scripting the following lines of code:

Let vRange = 3;

Do While vRange <= 3

     Orders_tmp2:

     LOAD

          *,

          RangeAmount * ChanceOfOrder as WeightedAmount

     ;

     LOAD

          *,

          $(vRange) as Range,

          if(Order_Date <= AddMonths(Date(Calendar_Date), $(vRange)), Amount) as RangeAmount

     Resident

          Orders_tmp1

     ;

     vRange = vRange * 2;

Loop

Oh, I forgot... ChanceOfOrder is an additional field in the Order table.

With this solution I could removed the set analysis date formula.

Hope that helps any other.

Regards