| Author |
Message |
gmooney10
Newbie
Joined: 29 Oct 2009
Location: United States
Online Status: Offline
Posts: 6
|
   Topic: Drilldown formula
Posted: 29 Oct 2009 at 1:21pm |
New to CR 2008. Have a simple formula that I want to create that takes ("Sales_Current" - "Sales_Previous")/(Sales_Previous) = Sales % Change
I need this to be able to calculate at any group level I drill down and I also need to wrap a statement to address a division by zero error should "Sales_Previous" = 0.
Having a difficult time accomplishing this......
Is drilldowngrouplevel the only way to get a formula to calculate based on the data within each level? If so, it is the division by zero issue that isn't working with my drilldowngrouplevel function.
Any suggestions would be greatly appreciated....
|
 IP Logged |
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 29 Oct 2009 at 1:28pm |
I only use the drilldowngrouplevel for display or hiding, not in any calculation formulas. I do not think it will work for you there.
You do calculations at group levels (which can be displayed with or without drilling down) by
calculation( field_to_calculate, groupfield)
e.g.
SUM({table.amount}, {table.salesrep})
COUNT({table.customers}, {table.slesrep})
Make sense?
Edited by DBlank - 29 Oct 2009 at 1:28pm
|
|
IP Logged |
|
gmooney10
Newbie
Joined: 29 Oct 2009
Location: United States
Online Status: Offline
Posts: 6
|
Posted: 29 Oct 2009 at 1:33pm |
|
SO if I want to have this field show up at 4 different group levels of my report I should write the formula 4 times since the groupfield will change each time?
|
|
IP Logged |
|
gmooney10
Newbie
Joined: 29 Oct 2009
Location: United States
Online Status: Offline
Posts: 6
|
Posted: 29 Oct 2009 at 1:39pm |
Seems like what you are saying is to create this?
if {Fct_Div_GTIN_Week.Sales_Prev},{Dim_Mgt_Div.Mgmt_Div_Description}=0 then 0 else{Fct_Div_GTIN_Week.Sales_Curr},{Dim_Mgt_Div.Mgmt_Div_Description}
- {Fct_Div_GTIN_Week.Sales_Prev} / {Fct_Div_GTIN_Week.Sales_Prev}
But this gives me errors......
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 29 Oct 2009 at 1:46pm |
Depends on what you mean by Group Level.
If you have your design like this
Gh1
GH2
GH3
GH4
Details
GF4
GF3
GF2
GF1
and you want each level summed
if you just have 4 possible answers in group1 then no you only make one formula.
Your second post is hard to decipher as I do not know your data but it is missing the summarized functions :
if SUM({Fct_Div_GTIN_Week.Sales_Prev},{Dim_Mgt_Div.Mgmt_Div_Description})=0 then 0 else
SUM({Fct_Div_GTIN_Week.Sales_Curr},{Dim_Mgt_Div.Mgmt_Div_Description})
Post a a little row level sample data and how you want it summarized.
|
|
IP Logged |
|
gmooney10
Newbie
Joined: 29 Oct 2009
Location: United States
Online Status: Offline
Posts: 6
|
Posted: 29 Oct 2009 at 2:04pm |
Here are 3 levels of my groups:
|
A |
B |
C |
|
Gh1 |
$35,163,517 |
$34,843,487 |
0.9% |
| GH2 |
$11,133,097 |
$12,478,274 |
-10.8% |
| GH3 |
$511,128 |
$0 |
#DIV/0! |
| GH4 |
|
|
|
| Details |
|
|
|
| GF4 |
|
|
|
| GF3 |
|
|
|
| GF2 |
|
|
|
| GF1
|
|
|
|
Column C is the calculated formula of (A-B)/B so I am assuming that I need to write that formula for each group? I also need to account for making the #Div/0! a zero.
Thanks for your help so far!!!!
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 29 Oct 2009 at 2:12pm |
Yes you will need 4 formulas (1 per group level).
Your division by 0 is simply an "if then" per SUM(fieldB,groupedfield)...
Note I am assuming you are summing here...
Group1:
if SUM(fieldB,field used for group1)=0 then 0 else
(SUM(fieldA,field used for group1) - SUM(fieldB,field used for group1)) % SUM(fieldB,field used for group1)
Group2:
if SUM(fieldB,field used for group2)=0 then 0 else
(SUM(fieldA,field used for group2) - SUM(fieldB,field used for group2)) % SUM(fieldB,field used for group2)
etc. for 3 and 4
Edited by DBlank - 29 Oct 2009 at 2:12pm
|
|
IP Logged |
|
gmooney10
Newbie
Joined: 29 Oct 2009
Location: United States
Online Status: Offline
Posts: 6
|
Posted: 29 Oct 2009 at 2:31pm |
Thank you a million....this works great except having to write the formula 4 times for each group.
I took what you gave me and wrapped the drilldowngrouplevel into this so that I would not have to write 4 formulas:
if drilldowngrouplevel = 0 then if SUM({Fct_Div_GTIN_Week.Sales_Prev},{Dim_Mgt_Div.Mgmt_Div_Num})=0 then 0 else
(SUM({Fct_Div_GTIN_Week.Sales_Curr},{Dim_Mgt_Div.Mgmt_Div_Num}) - SUM({Fct_Div_GTIN_Week.Sales_Prev},{Dim_Mgt_Div.Mgmt_Div_Num})) % SUM({Fct_Div_GTIN_Week.Sales_Prev},{Dim_Mgt_Div.Mgmt_Div_Num})
else if drilldowngrouplevel = 1 then if SUM({Fct_Div_GTIN_Week.Sales_Prev},{Dim_Mgt_Div.Mgmt_Div_Num})=0 then 0 else
(SUM({Fct_Div_GTIN_Week.Sales_Curr},{Dim_Base_GTIN.Segment}) - SUM({Fct_Div_GTIN_Week.Sales_Prev},{Dim_Base_GTIN.Segment})) % SUM({Fct_Div_GTIN_Week.Sales_Prev},{Dim_Base_GTIN.Segment})
else if drilldowngrouplevel = 2 then if SUM({Fct_Div_GTIN_Week.Sales_Prev},{Dim_Mfg.Mfg_Name})=0 then 0 else
(SUM({Fct_Div_GTIN_Week.Sales_Curr},{Dim_Mfg.Mfg_Name}) - SUM({Fct_Div_GTIN_Week.Sales_Prev},{Dim_Mfg.Mfg_Name})) % SUM({Fct_Div_GTIN_Week.Sales_Prev},{Dim_Mfg.Mfg_Name})
else 0
This works great except that when I drill down from level 1 to level 2 the level 2 formulas are correct but the level (group1 which is still showing on group2 page) now shows the incorrect formula and shows the result for the first entry in level 2?
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 29 Oct 2009 at 2:39pm |
Drilldowngrouplevel is not equivalent to group levels (although it can end up that way if the user drills down in order).
Sorry but you will need 4 formulas. Just copy and paste then swap out the group level field name.
Drilldowngrouplevel is 0 when you open the report, then 1 when you drill down (regardless of which level you pick) then 2 if you drilldown again, etc.
In my experience it is used to change format appearances or suppress sections or fields based on drilling down and not in calculation formulas. it is too user dependent to work correctly for that.
For each of your formulas you will palce them on each group header or footer (depending on which group level field you used).
Edited by DBlank - 29 Oct 2009 at 2:40pm
|
|
IP Logged |
|
gmooney10
Newbie
Joined: 29 Oct 2009
Location: United States
Online Status: Offline
Posts: 6
|
Posted: 29 Oct 2009 at 2:50pm |
|
Thank you very much...you saved me a ton of time......
|
|
IP Logged |
|
|
|
