Cross tab formula?

[cristianivanoff]

I have this formula in a cross tab with the Distinct count as summary type:

numbervar nummer:=12;
if {Melody_subs_status_log.affective_from}<=date(dateadd("m",-nummer,{?SlutDatum}))
and not ({Melody_subs.subs_status_id} in [3,7])
and (isnull({Melody_subs_status_log.affective_to}) or {Melody_subs_status_log.affective_to}>date(dateadd("m",-nummer,{?SlutDatum})))
then {Melody_subs.subs_id}

This work fine. But for these 'Distinct' {Melody_subs.subs_id} I also need to do a calculation. For every {Melody_subs.subs_id} that is in the Distinct count I need to calculate 'Melody_subs.recurring_price*Melody_subs.attributenr'. How can I do this?

I use CR9.

BR
cristian

 

[lbass]

Can you explain why you are not filtering at the report level instead of within the crosstab? This might be simpler if you inserted a subreport, filtered in the record selection formula of the sub, and then inserted the crosstab.

Note that a distinctcount of your first formula will not give you correct results whenever there are records in the row group that do not meet your criteria, since that cluster of records will be counted as 1. To correct for that, you can create a formula {@null} by opening a new formula and saving and closing without entering anything. Then change your formula to:

numbervar nummer:=12;
if {Melody_subs_status_log.affective_from}<=date(dateadd("m",-nummer,{?SlutDatum}))
and not ({Melody_subs.subs_status_id} in [3,7])
and (isnull({Melody_subs_status_log.affective_to}) or {Melody_subs_status_log.affective_to}>date(dateadd("m",-nummer,{?SlutDatum})))
then {Melody_subs.subs_id} else tonumber({@null}) //assuming that subs_id is a number

For further help, please identify your row field(s) and column fields in the crosstab.

-LB

 

[cristianivanoff]

I have the same formula several times in the same cross tab with different Numbervar. Twelve formulas, one for each month in columns. I don't have any column field and in the row field I have a formula for grouping my products. What the formula above is supposed to do is to find a 'Subs ID' that was not in status terminated (=3) or termination in progress (=7) twelve month ago. So if I use this formula with a distinct count I should get all the subs ID's that meet that criteria (+1).

 

[lbass]

The problem is that sometimes the formula will be off by 1 and sometimes it will be correct (when all records meet your criteria).

In CR 9, can you use running totals in crosstabs?

-LB

 

[cristianivanoff]

yes I can.

 

[lbass]

I would use running totals for both calculations then. First create a formula {@prcalc} like this:

{Melody_subs.recurring_price}*{Melody_subs.attributenr}

For your first summary, insert a running total that uses a distinctcount of {Melody_subs.subs_id}, evaluate using a formula:

numbervar nummer:=12;
{Melody_subs_status_log.affective_from} <= date(dateadd("m",-nummer,{?SlutDatum})) and
not({Melody_subs.subs_status_id} in [3,7]) and
(
isnull({Melody_subs_status_log.affective_to}) or {Melody_subs_status_log.affective_to} > date(dateadd("m",-nummer,{?SlutDatum})
)

Reset on change of the product group (or field) (whatever your row field is).

For the calculation, select {@prcalc}, sum, evaluate on change of {Melody_subs.subs_id}, reset on change of product.

I think the main report will need to be sorted or grouped first by product and then by {Melody_subs.subs_id}.

Then add these rts into the crosstab in the report footer.

-LB

 

[cristianivanoff]

ok, there's only one problem left. With a running total I just add a new amount of subs ID's to the group.
So if if the amount of month_1 is 10 then the amount of month_2 is (10+x).

Suppose that 2 of the first 10 does not meet the criteria in month_2 then it should be 8+x instead of 10+x. The reason for this is that in month_1 there are 10 subscriptions (subs ID) in status 'in service' (=not in 3 or 7) and of those 10 only 8 are in service in Month_2. You see, the status can change over time and the running total does not take that in consideration.

Don't know if I can explain better. I hope you understand.

Maybe I have to make some view to prepare the data in some way...

BR
Cristian

 

[lbass]

No I'm not following. You would have separate running totals for each month.

-LB

 

[cristianivanoff]

Yes I would need separate running totals for each month. How can do this?

/cristian

 

[lbass]

Previously, I believe you were using separate formulas for each month, adding these as your summaries, and formatting the crosstab to show a horizontal display of the summaries. Now you are just creating separate running totals instead, each with a different evaluation formula, where you change your numbervar.

-LB