Cross-tab formula, please help me..

[cristianivanoff]

Hello all!
I have two questions (excuse my English):
1.
I have a cross-tab with three formulas in each column. I want a fourth formula to divide the result (the total) of two previous formulas.

Formula1|Formula2|Formula3|Formula4
100, 200, 300, 200/300

I thought i just could divide formula2 with formula 3 but the result is not correct. Anybody have a suggestion how to solve this? Could this be solved with 'gridrowcolumnvalue'? How?

2.
I have a cross-tab with a date field (Subs_affective_from) in the column header grouped by month. The summarized field is a 'Distinct count' of SubsID.
In the row header I have a formula called @Status (it doesn't work).
@Status:
if {subs_Satus}=4 then "New" else if {subs_Status}=3 then "Churn"
else if {subs_Status} in [11,15] and {Subs_affective_from}<gridrowcolumnvalue("{Subs_affective_from}") and {Subs_affective_to}>=gridrowcolumnvalue("{Subs_affective_from}")
then "Base"

The formula doesn't work. But it shows what I want to do. The problem is that I am trying to compare the affective_from field with it self. Do you have any suggestions to my problem?


BR
Cristian

 

[lbass]

For the first crosstab, please identify the row, column, and summary fields, and show the contents of your formulas.

What are you trying to do with your row formula in the second crosstab? A detail row can't be both in the range defined by the column and outside the range as defined by the row.

-LB

 

[cristianivanoff]

ok, thank you for the help. I'm a beginner in CR.

1) I have a date field in the column heading grouped by month. In the row heading I have product name. In the summary field I have three formulas:
Sum Formula1: {Revenue}/1000
Sum Formula2: {Minutes}/1000
Sum Formula3: {number_of_calls}/1000

That is, simple database fields in the formulas.

Now I would like to have a fourth formula which divides the result of formula2 and formula3.
If my formula4 is: @Formula2/@Formula3 I don't get a correct result because of it summarizes each result. I simply want the total result of formula2 divided by the total result of formula3.

2) The second cross-tab is a problem. I have a table like this:
SubsID, StatusID,Status_affective_from,status_affective_to

I want to group my subsID's in three groups: New,Churn and Base.
For each month I want to know how many subsID have been set to status=4 (=New), how many subsID have been set to status=3 (=churn) and how many subsID are installed (base) in total, not how many have been set to this status the actual month. The 'Base' status is 11 and 15.

So, I chose the affective_from field in the column header, and my group formula in the row header. This work fine if the group formula only contain the conditions for New and Churn.

For status Base: I want the affective_from date (the date when this SubsID was set to status 11 or 15) for these subsID to be older than the range in the column header and the affective_to date to be 'greater' than the range in the column header. This is why my formula is so strange. I would have to compare the date with it self.

In the summary field I have 'dinstict count' of SubsID.

Does this make any sense?
Could this be done in another way?

 

[lbass]

Issue 1:

Create a formula {@0} to act as a holder summary:

whilereadingrecords;
0

Insert this formula as your fourth summary field. Then in preview mode, select formula 2->format field->common->suppess->x+2 and enter:

whileprintingrecords;
numbervar form2 := currentfieldvalue;
false

Then select formula 3->format field->common->suppess->x+2 and enter:

whileprintingrecords;
numbervar form3 := currentfieldvalue;
false

Then select {@0}format field->common->DISPLAY STRING->x+2 and enter:

whileprintingrecords;
numbervar form2;
numbervar form3;
totext(form2/form3,2)

In each case above, if you want the same thing in a total row, select the inner cell and the column total simultaneously when entering the formulas.

Issue 2:

Do you have a periods table to work with that would contain dates independent of your two date fields?

-LB

 

[cristianivanoff]

Thank you for your help. I will try this. And yes I have a date table that contains dates independent of my two date fields. The problem is that I dont how to use it.
Date table: Date|NextDate|YearNumber|MonthNumber|DayNumber|

Any help with the second issue would be helpfull because im really stuck.

BR
Cristian

 

[cristianivanoff]

I have been looking at this date table but i don't really know how to join it. Is it possible to use it without joining the tables? Any way I'm very greatfull for your help.

 

[lbass]

On second thought, I'm not sure the periods table really helps for this issue. You might be better off creating a manual crosstab for this purpose. You would first insert a group on the following formula {@status}:

Select {table.status}
case 4 : "New"
case 3 : "Churn"
case 11, 15 : "Base"

Then create a formula for each month like this:

if
(
{table.status} in 3 to 4 and
month({table.affective_from}) = 1 and
year({table.affective_from}) = 2006
) or
(
{table.status} in [11,15] and
{table.affective_from} < date(2006,1,1) and
{table.affective_to} > date(2006,1,31)
) then
{table.subsID} else
tonumber({@null}) //Jan 2006

...where {@null} is a formula that has been created with nothing saved for content. The above assumes that subsID is a number; if it isn't, remove the tonumber(). Then place a formula like the above for each month in the detail section and then right click on each and insert a summary (distinctcount) at the status group level. Then suppress the detail section.

One comment: You are requiring for the "Base" that subsID be "active" for the entire month. If instead you wanted to pick up a case that was active for at least some part of a month, you would instead use:

{table.affective_from} < date(2006,2,1) and
{table.affective_to} >= date(2006,1,1)

-LB

 

[cristianivanoff]

Thank you. This works fine. Both of the issues. I did some own changes though (issue 2) but it really works. Now I have 6 formulas (one for each month) and the user enters the 'last' month trough a parameter. I used dateadd() to calculate the new date. How can make a chart with these formulas as 'data' where the x-axis is month and y-axis is volume? Is this possible?

Again. Thank you for your help.

Br
Cristian

 

[lbass]

Try inserting the chart in the report footer and then add each formula as a summary field, with no on change of field (since that is built into the formulas).

-LB

 

[cristianivanoff]

Last question. I would like to have a 'negative' distinct count if it is 'Churn'. Is this possible to do?

Thank you

BR
Cristian

 

[lbass]

Where do you want this negative value? It's easy enough to display a negative value, but do you want to chart it?

-LB

 

[cristianivanoff]

Yes I would like to chart it and display it negative. I cant find out a way to do this.

Br
Cristian

 

[lbass]

I think you would have to create formulas like:

if {@status} = "Churn" then
-distinctcount({@Jan}) else
distinctcount({@Jan})

...and then use these for the summary fields with "do not summarize" checked. Not sure, didn't test this.

On second thought, you might want to insert the chart as suggested earlier into the group footer for {@status}--not sure. Or try placing it in the report footer with {@status} as the "on change of" field.

-LB