-
1. Re: Hide blank column when showing year-over-year values and % difference
Joe Mako Apr 12, 2011 8:15 AM (in response to Aaron Rubinstein)How about the attached?
-
example_calculations.twbx 32.6 KB
-
-
2. Re: Hide blank column when showing year-over-year values and % difference
Aaron Rubinstein Apr 12, 2011 2:02 PM (in response to Aaron Rubinstein)Perfect, thanks Joe!
-
3. Re: Hide blank column when showing year-over-year values and % difference
Aaron Rubinstein Apr 12, 2011 2:30 PM (in response to Aaron Rubinstein)Just tried to set this up and I ran into a problem. In your example workbook you created calculated fields for the 2009 and 2010 sales amounts using the formula: IIF(YEAR([Order Date])=2009,[Sales],0)
My sales are actually in fiscal years rather than calendar years so the YEAR[Order Date]=2009 does not isolate the correct values. Any thoughts on how to work around this?
Thanks!
-
4. Re: Hide blank column when showing year-over-year values and % difference
Aaron Rubinstein Apr 12, 2011 4:38 PM (in response to Aaron Rubinstein)Update: I was able to work around the problem by replacing the YEAR function with an actual date range for my fiscal year.
-
5. Re: Hide blank column when showing year-over-year values and % difference
Aaron Rubinstein Apr 12, 2011 8:40 PM (in response to Aaron Rubinstein)Another issue: I am trying to create the same year-over-year analysis, this time counting the number of expense reports submitted by business unit in 2009 vs. 2010 with % difference. To get a unique count of expense reports I created a calculated field called "Expense Report Count" using the formula: COUNTD([Report Number])
Using Joe's suggestion above, I then tried to create calculated fields for the fiscal 2009 and fiscal 2010 # of unique expense reports using the following formula:
Unique fiscal 2010 expense reports: IIF([Expense Date] >= #August 1, 2009# AND [Expense Date] <= #July 31, 2010#, [Expense Report Count], 0)
This function is giving me an error: "Cannot mix aggregate and non-aggregate arguments".
Any ideas on how to accomplish this would be greately appreciated!
-
example.png 5.5 KB
-
-
6. Re: Hide blank column when showing year-over-year values and % difference
Joe Mako Apr 12, 2011 9:03 PM (in response to Aaron Rubinstein)How about calculated fields like:
COUNTD( IF [Expense Date] >= #August 1, 2009# AND [Expense Date] <= #July 31, 2010# THEN [Report Number] END)
This is returning the distinct count of "Report Number" values between those two dates
-
7. Re: Hide blank column when showing year-over-year values and % difference
Aaron Rubinstein Apr 13, 2011 7:51 AM (in response to Aaron Rubinstein)That worked, thanks again!
-
8. Re: Hide blank column when showing year-over-year values and % difference
Paul Lazard Jun 22, 2011 2:46 AM (in response to Aaron Rubinstein)Hi all. My first (of probably very many!) posts!! Unfortunately the attachments are unavailable so I cannot see Joe's answer to this problem. I have the same question, although I want to see the difference between two companies (which I have set up as a filter) rather than year on year differences.
Thanks in advance
-
9. Re: Hide blank column when showing year-over-year values and % difference
Joe Mako Jun 22, 2011 6:46 AM (in response to Aaron Rubinstein)Paul, attached is the workbook I uploaded on April 12th.
What you will want to modify is the calculated fields named "2009 Sales" and "2010 Sales" from
IIF(YEAR([Order Date])=2009,[Sales],0) to something like: IIF([Company]="Company of Interest",[Sales],0) or you can use a parameter instead of the static text string.
-
example_calculations.twbx 32.6 KB
-