Announcements
cancel
Showing results for
Did you mean:
Contributor II

## Create histogram using filtering and based on distinct values from other column

Say I have a table looking like

``````id  age  date
--+----+-----
1   20   2021-01-01
1   20   2021-02-01
2   30   2021-01-01
3   22   2021-03-15
3   22   2021-04-16``````

I then want to make a histogram of the "age" for distinct "id" which have a "date" less than,say, "2021-02-01".

I have tried :

("dates" is date-varibale from another table that the user uses to set the upper-bound of the date)

=AGGR(IF(date<dates,age),id)

but I just get the error in the chart:

I have also tried set-analysis:

=AGGR({\$<date={"<\$(=Max(dates))"}>} age,id)

but that gives the same error

MVP

Hi,

Find example :

PeopleTemp:
PersonID, Person
1, Jane
2, Joe
3, Shawn
4, Sue
5, Frank
6, Mike
7, Gloria
8, Mary
9, Steven,
10, Bill
];

AgeTemp:
PersonID, Age
1, 23
2, 45
3, 43
4, 30
5, 40
6, 32
7, 45
8, 54
9,
10, 61
11, 21
12, 39
];

People:
PersonID,
Person
Resident PeopleTemp;

Drop Table PeopleTemp;

//Add age and age bucket fields to the People table
Left Join (People)
PersonID,
Age,
If(IsNull(Age) or Age='', Dual('No age', 5),
If(Age<25, Dual('Under 25', 1),
If(Age>=25 and Age <35, Dual('25-34', 2),
If(Age>=35 and Age<50, Dual('35-49' , 3),
If(Age>=50, Dual('50 or over', 4)
))))) as AgeBucket
Resident AgeTemp
Where Exists(PersonID);

DROP Table AgeTemp;

Take : Agbucket in dimension

This way you can create histogram chart.