Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi,
Find example :
//Add dummy people data
PeopleTemp:
LOAD * INLINE [
PersonID, Person
1, Jane
2, Joe
3, Shawn
4, Sue
5, Frank
6, Mike
7, Gloria
8, Mary
9, Steven,
10, Bill
];
//Add dummy age data
AgeTemp:
LOAD * INLINE [
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
];
//LOAD new table with people
People:
NoConcatenate LOAD
PersonID,
Person
Resident PeopleTemp;
Drop Table PeopleTemp;
//Add age and age bucket fields to the People table
Left Join (People)
LOAD
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.
Hope this will help you
Vikas