TM1 Forum

Hi,

I'm kind of a newbie in tm1 and having an issue with a measure calculation

My aim is to find if a customer is regular or not = active 1 year ago AND active now. I'm ok at level 0 for customers, my issue is for level >0

see my exemple :
Customer1 active1yearago=1 activenow=0
Customer2 active1yearago=1 activenow=1
Customer3 active1yearago=1 activenow=1
Customer4 active1yearago=0 activenow=1

I say regular = min (active1yearago, activenow)
->
Customer1 regular=0
Customer2 regular=1
Customer3 regular=1
Customer4 regular=0

So I expect regular for Total Customers to be 2 but right now it returns 3. I mean it calculates for Total customers active1yearago=3 and activenow=3 -> 3

I need to say that for level>0 of customers, it has to be the sum of the children, but can't find a way to figure it out... Any help ?

(it is a simple exemple, i got more dimensions, etc... )

Stéphane

Please read the request for assistance guidelines and always include the actual code to enable people to help effectively rather than on one leg, blindfolded, with hands behind our backs.

Stéphane wrote:I need to say that for level>0 of customers, it has to be the sum of the children, but can't find a way to figure it out... Any help ?

However, from what I can guess from this bit of your description your rule is without N: or C: filters and all you need to do is limit the rule to N: only. That is assuming your time periods are also leaf - if not then you will need ConsolidateChildren on the customer dimension to get the result that you want.

Ok sorry about that.

Yes in my example you could solve with regular = N:min (active1yearago, activenow) and it works.

Actually it's a little more complicated as If 'm taking my whole cube and rules right now:

['conso actif à date']=ConsolidatedCountUnique(0,'mk_societes','MK_STATUT_CLIENT2',!entite, !lieu, !MK_PRODUITS_LIGHT, !mk_societes, !mois_annee, 'actif à date');

['conso actif 12 mois']=ConsolidatedCountUnique(0,'mk_societes','MK_STATUT_CLIENT2',!entite, !lieu, !MK_PRODUITS_LIGHT, !mk_societes, !mois_annee, 'actif 12 mois');

['fidèle 12 mois']=min(['conso actif 12 mois'],['conso actif à date']);

actif à date means active now
actif 12 mois means active 1 year ago
fidèle 12 mois means regular

If i'm putting an N filter :
['fidèle 12 mois']=N:min(['conso actif 12 mois'],['conso actif à date']);

it does not work (as ConsolidatedCountUnique only returns value on C members) -> all is equal to 0

my aim would be that on dimension 'mk_societes' if my level is >=1 that ['fidèle 12 mois'] is the sum of his children.

Ok got it.

['fidèle 12 mois']=IF(ELLEV('mk_societes',!mk_societes)=0,min(['conso actif 12 mois'],['conso actif à date']),ConsolidateChildren('mk_societes'));

Thanx