Calculated dimension with if only showing one option or null value

leenlart — Mon, 21 Nov 2022 09:06:19 GMT

Hi,

I'm working on creating a calculated dimension to show new clients 'Conquêtes' vs loyal clients 'Fidélisation'.

New clients are anyone who had zero sales the previous year.

Loyal clients had sales the previous year.

Easy-peasy right? .... Well, there is something I'm not doing right....

My calculated dimension looks like this :

=aggr(if( Sum({<TYPE_CALENDRIER={'N'},ANNEE={$(=ANNEE-1)}, MOIS=,SOURCE={'Ventes'}>} Volume)=0,'Conquête','Fidélisation'),CLIENT_ID,[SOUS-FAMILLE])

I'm grouping by client and Item sub-family because these are both used in my final object.

My problem is that the 'Conquete' part is not working, if a client has no sales in the previous year, it is coming up as a 'null' value in the table.

Example : the client below has no sales in N-1, but instead of the Fidélisation/Conquete field showing 'Conquête' it is showing a null value.

I tried the aggr the other way around, but to no avail.

=aggr(if( Sum({<TYPE_CALENDRIER={'N'},ANNEE={$(=ANNEE-1)}, MOIS=,SOURCE={'Ventes'}>} Volume)>0,'Fidélisation', 'Conquête'),CLIENT_ID,[SOUS-FAMILLE])

Should I test for zero or null for the Conquetes ?

topic Calculated dimension with if only showing one option or null value in App Development

Calculated dimension with if only showing one option or null value