aggr + total

[Daora342423]

Hi all, i have 4 column in my table (1,2,3,4 where 4 is the most specific), and i'm trying to do this:

sum( total <1>

    aggr( 

         sum( total <1,2,3>

                   aggr(

                                 if(), 1,2,3,4)

                    )

          , 1,2)

    )

but the result is wrong, if i try to do the same but in this way:

sum( total <1>

    aggr( 

         sum( total <1,2,3>

                   aggr(

                                 if(), 1,2,3,4)

                    )

          , 1,2,3)

    )

the result is ok but i can't count on 2 time the same combo 1-2 (cause 3 is more specific and i can have 1 = 'A', 2 = 1, 3 = '1B' and also 1 = 'A', 2 = 1, 3 = '1C')

how can i resolve this?

thanks all

[sunny_talwar]

I am not sure what exactly is the issue, but you can look into using NODISTINCT with Aggr() to finding a solution for this.

[marcus_sommer]

I suggest that you before continuing checks if you really need such nested aggr-constructs to get your wanted results and that your data-model is suitable for this kind of analysis. Even if such approach worked it could become very slow by already mid-sized datasets and in larger ones it's often deathly ...

Beside this if you want to apply different calculations for different dimensions you could query them with dimensionality() and branching then appropriate.

- Marcus

[Daora342423]

this is my code:


sum(total<[RST Codice]>
aggr( distinct
if( min({<[Classificazione contratto]={'CONTO'},[Classificazione Prodotto]={'Liq. No MAX', 'Amm. No MAX'}>} [PTF Controvalore] ) < 0
and
sum(total<[RST Codice], [Codice Cliente]>
aggr(
if(min({<[Classificazione contratto]={'CONTO'},[Classificazione Prodotto]={'Liq. No MAX', 'Amm. No MAX'}>}[PTF Controvalore]) < 0, 1 ), [RST Codice], [Codice Cliente],[Codice Contratto], AnnoMeseCifre ))
/
count(total distinct AnnoMeseCifre) > vB4RPercMesi , 1 ), [RST Codice], [Codice Cliente], [Codice Contratto]))

in this way i can count in the right way thx the dimention "codice contratto" in the aggr, but i don't need to count that dimension, i need to count how many "codice cliente" i have for every "rst codice", if i drop [Codice Contratto] of the last aggr, the count fail with a 9 as resoult, my resoult is 7, i have 7 "codice cliente" and i don't understand why this won't work:


sum(total<[RST Codice]>
aggr( distinct
if( min({<[Classificazione contratto]={'CONTO'},[Classificazione Prodotto]={'Liq. No MAX', 'Amm. No MAX'}>} [PTF Controvalore] ) < 0
and
sum(total<[RST Codice], [Codice Cliente]>
aggr(
if(min({<[Classificazione contratto]={'CONTO'},[Classificazione Prodotto]={'Liq. No MAX', 'Amm. No MAX'}>}[PTF Controvalore]) < 0, 1 ), [RST Codice], [Codice Cliente],[Codice Contratto], AnnoMeseCifre ))
/
count(total distinct AnnoMeseCifre) > vB4RPercMesi , 1 ), [RST Codice], [Codice Cliente]))

[marcus_sommer]

I assume that without a quite deep diving into the dimensions of your table and the underlying datamodel it's rather difficult to give you an exact solution. Therefore the above suggestion to check your approach from the beginning. An aggr() should be nearly the last measure if really no other solutions are possible or practically.

- Marcus