Hi guys
I've been using for some months webfocus and stumbeld over probably a simply solvable problem.
When I try so define a subtotal for a report on some sort field a count sum seems to work fine, even a average could be implemented by making a compute field. But when a want to aggregate on a median option for example, the results of subtotals are getting simply summed up even when I use the recompute option.
Hier ist an example of it:

TABLE FILE CLUSTER_DM_GESUCH_ENTSCHEID_TEST
SUM
CNT.CLUSTER_DM_GESUCH_ENTSCHEID_TEST.DM_DIM_GESUCH_ENTSCHEID_GESENTREPTEST.ENTSCHEIDDAUER_INTAGEN_NUM AS 'Anzahl,Entscheide'
COMPUTE AVE_Entscheiddauer/D12.2 = CLUSTER_DM_GESUCH_ENTSCHEID_TEST.DM_DIM_GESUCH_ENTSCHEID_GESENTREPTEST.ENTSCHEIDDAUER_INTAGEN_NUM / CNT.CLUSTER_DM_GESUCH_ENTSCHEID_TEST.DM_DIM_GESUCH_ENTSCHEID_GESENTREPTEST.ENTSCHEIDDAUER_INTAGEN_NUM; AS 'Average,Durchschnittliche,Dauer,der Entscheide,in Tagen'
MDN.CLUSTER_DM_GESUCH_ENTSCHEID_TEST.DM_DIM_GESUCH_ENTSCHEID_GESENTREPTEST.ENTSCHEIDDAUER_INTAGEN_NUM AS 'Median,Entscheiddauer'
BY CLUSTER_DM_GESUCH_ENTSCHEID_TEST.DM_V_VERORTUNG_MITARBEITENDE_GESUCH_ENTSCHEID_GESENTREPTEST.ADRESSE_STRING AS 'Standort,MA'
BY CLUSTER_DM_GESUCH_ENTSCHEID_TEST.DM_V_MITARB_VERANTWORTLICH_GESUCH_GESENTREPTEST.VorNameMAStellen_string
BY CLUSTER_DM_GESUCH_ENTSCHEID_TEST.DM_V_KLIENT_GESUCH_GESENTREPTEST.VorNameKlient
ON CLUSTER_DM_GESUCH_ENTSCHEID_TEST.DM_V_MITARB_VERANTWORTLICH_GESUCH_GESENTREPTEST.VorNameMAStellen_string RECOMPUTE AS 'Total:'

Thanks for your help
Dragan

This message has been edited. Last edited by: Dragan,

The RECOMPUTE only apply on COMPUTEd fields, otherwise columns are SUMmed and all according to BY fields.

Then 'Anzahl,Entscheide' and 'Median,Entscheiddauer' will be SUMmed only and 'Average,Durchschnittliche,Dauer,der Entscheide,in Tagen' RECOMPUTEd based on VorNameMAStellen_string.

So I don't know what is your request then.

Hi Martin

Thank you for you reply.
The problem is when I try that the results of the median field in the recompute subtotal are just beeing SUMed instead of for every subtotal group computed.
for example
cost_car_x count:10 average:5$ median:5$
cost_car_y count:5 average:10$ median:8$
subtotal cost_car_xy count: 15 average 15$ median:13$

the average and the median should not just be summed to a total of 15$ or median 13$, but give a result of something about average 8$

I understand now that not computed fields can't be recomputed, but how can I implement a median for example for subtotals?
is it at all possible on webfocus?

Kind Regards
Dragan

With Focus, there is always multiple solution (normally )

Maybe something in MDN function do not allow to RECOMPUTE... I don't know.

Here, one option:

DEFINE FILE CAR
CNTRY /A16 = 'Total';
END
TABLE FILE CAR
SUM DEALER_COST
    CNT.SEATS   AS 'NB_SEATS'
	COMPUTE AVG_CST /D7 = DEALER_COST / CNT.SEATS;
    MDN.SEATS AS 'MEDIAN_SEATS'
BY TOTAL COMPUTE ID2     /I1  = 3;
BY CNTRY
BY TOTAL COMPUTE ID1     /I1  = 3;
BY TOTAL COMPUTE CAR     /A16 = '';
ON TABLE HOLD AS TMPGT FORMAT FOCUS
END
-RUN

DEFINE FILE CAR
CNTRY /A16 = COUNTRY || ' Total';
END
TABLE FILE CAR
SUM DEALER_COST
    CNT.SEATS   AS 'NB_SEATS'
	COMPUTE AVG_CST /D7 = DEALER_COST / CNT.SEATS;
    MDN.SEATS AS 'MEDIAN_SEATS'
BY TOTAL COMPUTE ID2     /I1  = 2;
BY CNTRY
BY TOTAL COMPUTE ID1     /I1  = 1;
BY TOTAL COMPUTE CAR     /A16 = 'Sub-Total';
ON TABLE HOLD AS TMPST FORMAT FOCUS
END
-RUN

DEFINE FILE CAR
CNTRY /A16 = COUNTRY;
END
TABLE FILE CAR
SUM DEALER_COST
    CNT.SEATS   AS 'NB_SEATS'
	COMPUTE AVG_CST /D7 = DEALER_COST / CNT.SEATS;
    MDN.SEATS AS 'MEDIAN_SEATS'
BY TOTAL COMPUTE ID2     /I1  = 1;
BY CNTRY
BY TOTAL COMPUTE ID1     /I1  = 1;
BY CAR
ON TABLE HOLD AS TMPDET FORMAT FOCUS
END
-RUN

TABLE FILE TMPDET
SUM DEALER_COST
    NB_SEATS
	AVG_CST
    MEDIAN_SEATS
BY ID1   NOPRINT
BY CNTRY AS 'COUNTRY'
BY ID2   NOPRINT
BY CAR
MORE
FILE TMPST
MORE
FILE TMPGT
END
-RUN

The idea is to create that data as you want it to be displayed (detail, sub-total, total), merge them together and display.

Dragan,

The issue is that you have to re-issue the mdn command. The way you do this is in the recompute phrase. See my example below.

SET SUMMARYLINES = NEW

TABLE FILE CAR
SUM DEALER_COST
	COMPUTE AVG_CST /D7 = DEALER_COST / CNT.SEATS;
    MDN.SEATS AS 'MEDIAN_SEATS'
BY COUNTRY
BY CAR
BY MODEL
ON CAR RECOMPUTE DEALER_COST AVG_CST MDN.SEATS AS 'TOTAL BY CAR'
END
  

Thank you Martin!

This is exactly the solution for the problem that I had.
We have an experienced webfocus programmer in our office who explaned me that wf thinks in tuples of data defined by sort and by statements.

As a proficient user of spss statistics application and othe stat app I believe that this is a major bug or a improvment aspect for webfocus otherwise every total and subtotal command for all aggregation options are useless. During the evauation of this product I didn't test for such things because they are more than a must criteria for me, it's elementary aspect of calculation. One even get wrong results if uses this option, as I could see on Erics example.

Does anybody know where can I ask for a development improvment of webfocus?

Kind Regards
Dragan

quote:

Originally posted by MartinY:
With Focus, there is always multiple solution (normally )

Maybe something in MDN function do not allow to RECOMPUTE... I don't know.

Here, one option:

DEFINE FILE CAR
CNTRY /A16 = 'Total';
END
TABLE FILE CAR
SUM DEALER_COST
    CNT.SEATS   AS 'NB_SEATS'
	COMPUTE AVG_CST /D7 = DEALER_COST / CNT.SEATS;
    MDN.SEATS AS 'MEDIAN_SEATS'
BY TOTAL COMPUTE ID2     /I1  = 3;
BY CNTRY
BY TOTAL COMPUTE ID1     /I1  = 3;
BY TOTAL COMPUTE CAR     /A16 = '';
ON TABLE HOLD AS TMPGT FORMAT FOCUS
END
-RUN

DEFINE FILE CAR
CNTRY /A16 = COUNTRY || ' Total';
END
TABLE FILE CAR
SUM DEALER_COST
    CNT.SEATS   AS 'NB_SEATS'
	COMPUTE AVG_CST /D7 = DEALER_COST / CNT.SEATS;
    MDN.SEATS AS 'MEDIAN_SEATS'
BY TOTAL COMPUTE ID2     /I1  = 2;
BY CNTRY
BY TOTAL COMPUTE ID1     /I1  = 1;
BY TOTAL COMPUTE CAR     /A16 = 'Sub-Total';
ON TABLE HOLD AS TMPST FORMAT FOCUS
END
-RUN

DEFINE FILE CAR
CNTRY /A16 = COUNTRY;
END
TABLE FILE CAR
SUM DEALER_COST
    CNT.SEATS   AS 'NB_SEATS'
	COMPUTE AVG_CST /D7 = DEALER_COST / CNT.SEATS;
    MDN.SEATS AS 'MEDIAN_SEATS'
BY TOTAL COMPUTE ID2     /I1  = 1;
BY CNTRY
BY TOTAL COMPUTE ID1     /I1  = 1;
BY CAR
ON TABLE HOLD AS TMPDET FORMAT FOCUS
END
-RUN

TABLE FILE TMPDET
SUM DEALER_COST
    NB_SEATS
	AVG_CST
    MEDIAN_SEATS
BY ID1   NOPRINT
BY CNTRY AS 'COUNTRY'
BY ID2   NOPRINT
BY CAR
MORE
FILE TMPST
MORE
FILE TMPGT
END
-RUN

The idea is to create that data as you want it to be displayed (detail, sub-total, total), merge them together and display.

Hi Eric

Unfortunately in you example the results of median are just beeing added to the total which is exactly the problem I am having. The median should be calculated for the whole total tuple not just adding the result.
Martin solution seems to be usefull.
Kind Regards
Dragn

quote:

Originally posted by eric.woerle:
Dragan,

The issue is that you have to re-issue the mdn command. The way you do this is in the recompute phrase. See my example below.

SET SUMMARYLINES = NEW

TABLE FILE CAR
SUM DEALER_COST
	COMPUTE AVG_CST /D7 = DEALER_COST / CNT.SEATS;
    MDN.SEATS AS 'MEDIAN_SEATS'
BY COUNTRY
BY CAR
BY MODEL
ON CAR RECOMPUTE DEALER_COST AVG_CST MDN.SEATS AS 'TOTAL BY CAR'
END
  

Hi Dragan,

You need to open a Case, explain your problem and ask for a New Feature Request (NFR).

Glad that my solution helps you.

Edit your first post then update the subject to add [SOLVED] at the beginning.

quote:

SET SUMMARYLINES = NEW
DEFINE FILE CAR
MEDIAN_SEATS/D12=SEATS;
END
TABLE FILE CAR
SUM DEALER_COST
CNT.SEATS
COMPUTE AVG_CST /D7 = DEALER_COST / CNT.SEATS;
CNT.MEDIAN_SEATS AS CNT_SEATS
MDN.MEDIAN_SEATS AS 'MEDIAN_SEATS'
BY COUNTRY
BY CAR
BY MODEL
ON CAR RECOMPUTE DEALER_COST AVG_CST CNT.MEDIAN_SEATS MDN.MEDIAN_SEATS AS 'TOTAL BY CAR'
END

Dragan,

The syntax is correct but there does appear to be a bug in the product. When I add in 'CNT.MEDIAN_SEATS' I get a median value of 2, when I don't have 'CNT.MEDIAN_SEATS' I get the values added together (7). I would open a case for this, as my syntax is much simpler and is the correct way to do it. But as you pointed out, it still isn't working correctly unless you try some workarounds.