Measure calculation question

[Stéphane]

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

[lotsaram]

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.

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.

[Stéphane]

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.

[Stéphane]

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