Set Analysis - Changing Date of Birth and Gender

[#1]

hi all,

in the underneath image you see a straight table.

Can you help me with the following requirements:

• i want create a dimension/expression which should create Age. which expression should i use if i use the column Date of Birth?
• I want to change the value Dhr. and Mevr. into : Dhr. = Male and Mevr = Female. which expression should i use to change these two dimension values?

Thanks regards!

Greets,

Rega

[swuehl · Accepted solution]

Try

=Age(Today(), [Date of Birth])

age(timestamp, date_of_birth)

Returns the age at the time of timestamp (in completed years) of somebody born on date_of_birth.

Examples:

age('2007-01-25', '2005-10-29') returns 1 

age('2007-10-29', '2005-10-29') returns 2 

[swuehl]

For the gender

=If(Gender= 'Dhr.','Male', 'Female')

I would suggest that you transform your data already in the script,if possible.

[#4]

hi

try like this

if(Gender='Dhr.','Male' ,

        if(Gender='Mevr','Female',Gender)) as Gender,

[#5]

Hi Swuehl,

should i use =Age(Today(), [Date of birth]) as a expression  or a dimension ?

[swuehl]

It probably works at both places, having Customer as dimension.

[#7]

Hi Guys,

i am stuck with setting a sort : top 5 in the underneath straight table. as you can see i already fill the value 5 for largest but still the straight table show more than 5 rows.

Which steps should i do now?

[swuehl]

Are you using Total Spent as dimension or expression?

The dimension limit is looking for Top5 values of the first expression.

Try and expression

=Sum([Total Spent])