Dynamic Dashboard - Aggregating Time periods.... Excel 2010 1

[Welshbird]

Morning all! It is I again.

Right, OK, so am in new job and trying to look clever.

Am beginning to love Excel 2010 - and have so far managed to use the SUMIFS() to create a lovely dynamic chart.

the data I have is fairly normalised in one worksheet with the following columns:

Region | Area | Product | Type | Month | Data

The month field is populated as the first of each month with real date formats (having listened to Skip far too many times not to do this!).

At the moment I have about 250K rows, and each row shows the data for one Region, Area, Product, Month and a metric.

However, we really want to look at rolling quarters (MQT) and rolling annual totals (MAT). So, for MQT's first, if I want March 09, I would expect this to show a total of Jan, Feb and Mar 09.

At the moment it seems to me that SUMIFS isn't the obvious answer - I guess I could wrap it in a big AND-IF statement, but am really looking for advice. It doesn't feel like the solution to create x-number of new rows for MQT and again for MAT.

Any thoughts gentlemen and ladies?

As always, thanks in advance.

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[SkipVought]

hi,

Tickled that you are on the strait & narrow! ;-)

At the moment it seems to me that SUMIFS isn't the obvious answer

Why not?
[tt]
=SUMIFS(SUMrange,MOQ, ">="&Date(Year(SelectedDT),Month(SelectedDT)-3,1),MOQ, "<="&Date(Year(SelectedDT),Month(SelectedDT)+1,0)
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Welshbird]

You a quite correct as usual....

Thaks again.

I'm at least being proud of asking for more general advice and then working out how to do all of the other bits myself!

You are a treasure. Twinkly thing coming your way.

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[SkipVought]

I like to see that kind of progress!

I knew that I just HAD to produce a good answer, for a Fee. ;-)



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Welshbird]

So far so good! I now know if I need to include each data line in an MQT or an MAT!

Hmm.. still stuck on one thing though.

I have 24 months of data from Apr 09 to Mar 11 (at the moment). So, I wouldn'rt produce an MAT before Mar 10 as there wouldn't be 12 months of data.

(Apologies - I'm not actually posting here for an answer - just helps my thinking process, and then when I come up with the answer I'll post back).

So, my date must be at least +12 months from the min(date).... I might be on to something here....

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[SkipVought]

consider the MAX function.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Welshbird]

So far, I have this:

=IF($C$1 = "Monthly", "Monthly",IF(AND(C$12>=(DATE(YEAR(MIN(Month)),MONTH(MIN(Month))+2,1))),(IF(AND($C$2>=DATE(YEAR(C$12),MONTH(C$12)-2,1),$C$2<=DATE(YEAR(C$12),MONTH(C$12),1)),"MQT","No")),"Nope"))

Need several more If's and the actual SUMIFS inlcuded....

But certainly getting there!

(The time periods are static, and in a name range called Month so I was heading down that direction...)



Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[Welshbird]

Right. Logic is now there and working. I simply (HA!) have to add in the SUMIFS statments in each case.

=IF($C$2="MQT",(IF(AND(C$13>=(DATE(YEAR(MIN(Month)),MONTH(MIN(Month))+2,1))),
(IF(AND($C$3>=DATE(YEAR(C$13),MONTH(C$13)-2,1),$C$3<=DATE(YEAR(C$13),MONTH(C$13),1)),"MQT","No")),"Nope")),
IF($C$2="Monthly","Monthly",
(IF(AND(C$13>=(DATE(YEAR(MIN(Month)),MONTH(MIN(Month))+11,1))),
(IF(AND($C$3>=DATE(YEAR(C$13),MONTH(C$13)-11,1),$C$3<=DATE(YEAR(C$13),MONTH(C$13),1)),"MAT","No")),
"Nope"))))

Skip - Should I be contemplating creating my own functions here to simplify the formula? I'm guessing that creating an MQT function and an MAT function might be worthwhile.

I'll have a peruse of dat t'interwebnet thing and see what I can find....

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[SkipVought]

I would avoid Range Names that are RESERVE WORDS like Date, Month, Year.

I think that you're maing this harder that it needs to be.

I have found that when I have some complex logic, it is better to write it out in pseudo-code. Every time I have an IF in my logic, I automatically insert the ELSE and END, in order to force me to think about the logic involved.

For instance in your formula, just to begin...
[tt]
=IF($C$1 = "Monthly", "Monthly",IF(AND(C$12>=(DATE(YEAR(MIN(Month)),MONTH(MIN(Month))+2,1))),(IF(AND($C$2>=DATE(YEAR(C$12),MONTH(C$12)-2,1),$C$2<=DATE(YEAR(C$12),MONTH(C$12),1)),"MQT","No")),"Nope"))


IF $C$1 = "Monthly" Then
Return "Monthly"
Else
IF C$12 >= MAX([The minimum DATE],[3 mos back from ?]) Then
Return "???"
Else
Return "Nope"
End if
End if
[/tt]
Once the IF structure is logically correct, THEN you can develope or fill in the formulas for [The minimum DATE] and [3 mos back from ?].

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Welshbird]

Ah. One see's.

OK - Good point about the names of the ranges - will sort that straight away.

The logic now definately works though. My next great fun issue is to use SUMIFS within that to sum 3 months or 12 months as a range. Just off on a Google-hunt as we speak!

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[SkipVought]

Maybe something like
[tt]
=SUMIFS(SUMrange,MOQ, ">="&MAX(Date(Year(SelectedDT),Month(SelectedDT)-3,1),Date(Year(MIN(MON)),Month(MIN(MON)),1)),MOQ, "<="&Date(Year(SelectedDT),Month(SelectedDT)+1,0)

SUMIF
MOQ >= MAX([3 months back],[minimum MON])
MOQ <= [SelectedDT]
[/tt]




Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Welshbird]

I see what you are saying, but if there aren't three whole months in the data I don't want to calculate this MQT at all, so if data goes
Jan09, Feb09, Mar09, Apr09 I want to show
#NA, ~#NA, MQT, MQT, etc,

so I have to first work out if I should calculate this before I calculate it.

Make sense?

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[SkipVought]

So you do not want to do this..
[tt]
Jan09, Feb09, Mar09, Apr09
^
|-Calculate Feb09-Apr09 (Full 3 mos)
^
|-Calculate Jan09-Mar09 (Full 3 mos)
^
|-Calculate Jan09-Feb09 (2 mos)
^
|- Calculate Jan09 (1 mo)
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Welshbird]

Not at all, no.

However, whilst this not be the most elegant solution in the world, I do finally have a formual that completely works!

Prepare yourselves chaps:

=IF($C$3="MQT",(IF(AND(C$5>=(DATE(YEAR(MIN(Mnth)),MONTH(MIN(Mnth))+2,1))),
(IF(AND(C$5>=DATE(YEAR(C$5),MONTH(C$5)-2,1),C$5<=DATE(YEAR(C$5),MONTH(C$5),1)),
SUMIFS(data,Mnth,
">="&DATE(YEAR(C$5),MONTH(C$5)-2,1),
Mnth,"<="&DATE(YEAR(C$5),MONTH(C$5),1),LINE,$B6,Type,$C$2),NA())),NA())),
IF($C$3="Monthly",SUMIFS(data,Mnth,C$5,LINE,$B6,Type,$C$2),
(IF(AND(C$5>=(DATE(YEAR(MIN(Mnth)),MONTH(MIN(Mnth))+11,1))),
(IF(AND(C$5>=DATE(YEAR(C$5),MONTH(C$5)-11,1),C$5<=DATE(YEAR(C$5),MONTH(C$5),1)),SUMIFS(data,Mnth,
">="&DATE(YEAR(C$5),MONTH(C$5)-11,1),
Mnth,"<="&DATE(YEAR(C$5),MONTH(C$5),1),LINE,$B6,Type,$C$2),NA())),
NA()))))

Now, it ain't pretty, that's for sure, but it certainly allows me to change the start month, the products shown, the metrics and teh time-types and just recalculates and redraws my graphs on the fly.

So I'm still a little proud of it!

I guess the next step is to see if I can create a UDF that would validate if an MQT is complete, and if so calculate this. that might just be for nezt week though....

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[Thargy]

Thus elegantly demonstrating why the database was invented - geck!
As clear as the bottom of a swamp, and as maintainable as a nuclear hand grenade, come back SQL, all is forgiven.

Regards

T

 

[Welshbird]

Thargy - As you already know, I can only agree with you.

However, basically this is a 'proof of concept' that I'm going to build to allow the end user to play, and see if this shows the required information. Once we are sure of this, then we'll build it properly. This was though, faster and cheaper innit.

We can give the developers more than just a spec you see!

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen