5 Replies Latest reply: Mar 2, 2016 9:18 AM by Franck Robert

How to calculate a total in a column

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

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:
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:
%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

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

• Re: How to calculate a total in a column

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

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

Yes, exactly !