topic Re: How to calculate a total in a column in App Development

How to calculate a total in a column

oxiofrt — Wed, 02 Mar 2016 12:32:26 GMT

Hi,

my table is a crossed one.

First dimension is a simple one, and second dimension is an AGGR one.

I am not able to add a calcultated total for the first dimension, that would not take the second dimension into account.

May you help ? An app is joined in this discussion.

Thanks a lot for you help. !

Re: How to calculate a total in a column

cesaraccardi — Wed, 02 Mar 2016 13:51:59 GMT

Hi Franck,

I think you might want to create the "AGGR" dimension on the script. Something like:

LOAD
  DT & '|' & ID_SECTEUR as %AGGR_DIMT,
    DATE_DEPART,
    DATE_DEPART_V,
    DT,
    HEURE,
    ID_CENTRE,
    left (SECTEUR,2) as ID_SECTEUR,
    NB_DEPARTS_ENGINS,
    DH_DEB_DIMT,
    HEURE_DEB_DIMT,
    DH_DEPART,
    FL_SEUIL,
    DH_FIN
FROM [lib://QVD/DIMT.qvd](qvd)
WHERE year(DT) = 2015
and month(DT) = 9
and ID_SECTEUR < 10;


TMP_AGGR_DIMT:
LOAD
  DT & '|' & ID_SECTEUR   as %AGGR_DIMT,
    sum(NB_DEPARTS_ENGINS) as AGGR_NB_DEPARTS_ENGINS,
  max(FL_SEUIL) as AGGR_FL_SEUIL
RESIDENT
  DIMT
GROUP BY
  DT,
    ID_SECTEUR;


AGGR_DIMT:
LOAD
  %AGGR_DIMT,
    SUM(AGGR_NB_DEPARTS_ENGINS * AGGR_FL_SEUIL) as AGGR_NB_DEPARTS_ENGINS
RESIDENT
  TMP_AGGR_DIMT
GROUP BY
  %AGGR_DIMT;

DROP TABLE TMP_AGGR_DIMT;

Re: How to calculate a total in a column

cesaraccardi — Wed, 02 Mar 2016 13:53:29 GMT

This way you should be able to do the calculations easily.

Re: How to calculate a total in a column

oxiofrt — Wed, 02 Mar 2016 14:03:08 GMT

Thanks for your help.

But calculating it within the script avoid possibilty to use as many combination of filters as it required.

The aim is to have this total without modifying the script.

Re: How to calculate a total in a column

cesaraccardi — Wed, 02 Mar 2016 14:16:24 GMT

Hi,

When selections are aplied to other filters they are basically going to narrow down the possibilities for DT and ID_SECTEUR and thus filtering the "AGGR_DMIT" table. So I believe that is not going to be a problem unless these other filters need to be taken into count on the aggregation function depending on selections, is that what you are saying?

Re: How to calculate a total in a column

oxiofrt — Wed, 02 Mar 2016 14:18:39 GMT

Yes, exactly !