Trouble with sum, aggr and dimensionality

Fri, 07 Jun 2013 09:26:47 GMT

Hi everybody.

I am stuck with partial sum that are not correct.

I know this trouble has been already addressed but I did not find an answer.

That's why I'm asking you.

Let me explain first that i want to add2 fields in a table

RES STAGE :

=sum(aggr(max(NBRE_STAGE),DATE_CONVOCATION,JOURNEE_PREVISION))

STAGE RESERVED :

=sum(aggr(max(STAGES_RESERVE),DATE_CONVOCATION,JOURNEE_PREVISION))

TOTAL :

=sum(NBRE_PEOPLE)

And then my final expression :

RESIDUAL:

=if ([STAGE RESERVED]=0, TOTAL,TOTAL+[RES STAGE])

I have only 3 dimensions in my table ; CITY, DATE_CONVOCATION and JOURNEE_PREVISION (AM or PM)

I have checked partial sum on CITY, and day by day the sum is correct, but by month or year it is false.

I also tried this with no success ;

=IF(Dimensionality()=1, // Si ON est dans le Calcul du total

IF (sum(aggr(max(STAGES_RESERVE),MONTH_PREVISION))=0,

sum(NBRE_PEOPLE),

sum(NBRE_PEOPLE)+sum(aggr(max(NBRE_STAGE),MOIS_PREVISION))

IF (sum(aggr(max(STAGES_RESERVE),DATE_CONVOCATION,JOURNEE_PREVISION))=0,

sum(NBRE_PEOPLE),

sum(NBRE_PEOPLE)+sum(aggr(max(NBRE_STAGE),DATE_CONVOCATION,JOURNEE_PREVISION))

)

=IF(sum(aggr(max(STAGES_RESERVE),DATE_CONVOCATION,JOURNEE_PREVISION))=0,

sum(NBRE_PEOPLE),

sum(NBRE_PEOPLE)+sum(aggr(max(NBRE_STAGE),DATE_CONVOCATION,JOURNEE_PREVISION)))

Do you see what's going wrong ?

topic Trouble with sum, aggr and dimensionality in QlikView