Comparing data from two different Time Periods

[camilo]

Hi,

I´m having some problems trying to compare the data from two different time periods (between years). I´m using set analysis but it compares exactly the same time periods.

I would like to compare time periods with one day difference, example:

2011

From day 2 to day 20

A want to compare this period with:

2010

From day 3 to day 21

I´m upploading one example hopping it helps to clarify what I´m saying.

Thanks!

[swuehl · Accepted solution]

Hi Camilo,

I think it could look like:

=sum({$ <

Date={">=$(=addmonths(min(Date),-12)+1)<=$(=addmonths(max(Date),-12)+1)"},

Year=, Day=, Month=

   >} Sales)

Regards,

Stefan

[camilo]

Thanks Stefan, in deed it works perfectly!

[camilo]

Hi Stefan,

Using your formula solved how to presents the sales info, now I´m trying to go one step forward and calculate for example the % Margin being:

%Margin= (Sales-Costs)/Sales

I´m trying to use the following but it doesn´t work:

=sum({$ <

Date={">=$(=addmonths(min(Date),-12)+1)<=$(=addmonths(max(Date),-12)+1)"},

Year=, Day=, Month=

   >} (Sales-Costs)/Sales)

[swuehl]

Hi Camilo,

what do you mean with "it doesn't work"? Syntax error / returning no results at all?

Besided that I think you should use something like

= (1 -

sum({$ <

Date={">=$(=addmonths(min(Date),-12)+1)<=$(=addmonths(max(Date),-12)+1)"},

Year=, Day=, Month=

    >} Costs) /

sum({$ <

Date={">=$(=addmonths(min(Date),-12)+1)<=$(=addmonths(max(Date),-12)+1)"},

Year=, Day=, Month=

    >} Sales))

i.e. putting the sum around each portion of (1-Costs/Sales), otherwise you would sum up  all margin % of each single row, resulting in ever increasing values (could get pretty large).

[camilo]

Exatlty Stefan,

I got huge numbers or I didn´t got anything, now your solution does the work but I don´t understand why it add up when I don´t use the "1-" and it works when I use it.

Thanks

[swuehl]

I believe it is not depending on the "1-", that's just an equivalent expression:

(1-cost/sales) = (sales-costs)/sales

I preferred the first, because I only need two sums then instead of three.

As said, the difference between our two versions is that I first sum up Costs and Sales over all record rows and then do the Margin% calculation (1-cost/sales), instead of doing the calculation per row and then summing up.

Or have I misunderstood your question?

[camilo]

Thanks Stefan, now I undersantd

[#9]

This calculation does not work for me. I get no results back at all. I know my dimension is valid. What should I check?

=sum({$<Date={">=$(=addmonths(min(Date),-12)+1)<=$(=addmonths(max(Date),-12)+1)"},Year=, Day=, Month=>} Cnt)

I figured out my problem was the Date needed to be the Date_Num field in my data. But I'm still confused on how this is supposed to work. Camilo, can you post your final solution? It would just nice to look and see how your graph ended like. Thanks.