Simulation with Calculated variable in Set Analysis in Graphics

[#1]

Hello,

I have a big challenge for my client.

We want use a dashboard for Simuations.

My indicator is a pyramid of age for Humans Ressources.

I have a filter Date, a table collabs, and a table Groups Age (Label, ID, Min Age, Max Age).

I want construct a pyramid where I can see the evolution of my collabs changing the Date.

I calcul their new age with a formule :

SET vAge_Date =floor((floor(((Date - [Date naissance])/365)*12) + 0.49999)/12);

I don't see why I can found the formule where I can construct my pyramid of age (It's a Histogram Graph) with my dimension Groups Age.

I have try with this formule but it's not wroks 😞

count($(vSetACollabPrésent) DISTINCT IF($(vAge_Date)> FieldValue('Min',1) AND $(vAge_Date)<= FieldValue('Max',1) ,MatriculeCollab))

Can you help me please?

Kind Regards,

Bruno

[#2]

When you put this statement in the script:

SET vAge_Date =floor((floor(((Date - [Date naissance])/365)*12) + 0.49999)/12);

It sets vAge_Date to whatever the solution is using all of the dates in [Date naissance] and it won't change based on your selection.

I've never used FieldValue( before but I think that getfieldselections( might work for you but I don't think that would solve all your problems...

One thing you could try is doing something like...

LOAD [Label],[ID], min([Age]) as 'Min Age', max([Age]) as 'Max Age'
RESIDENT sometable
GROUP BY [ID],[Age];

And do like count(distinct aggr(if([Date naissance]=[Min Date],[ID]),[ID]))

(I'm assuming [Date naissance] means the day they were born?
I don't know if the aggr is right or if it's even necessary but I put it there anyway.

But I'm having a hard time understanding exactly what you want to do so... it's possible nothing I said is helpful