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

jakobjensen — Wed, 19 Jan 2022 09:38:47 GMT

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

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

vikasmahajan — Fri, 28 Jan 2022 13:47:33 GMT

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

topic Create histogram using filtering and based on distinct values from other column in App Development

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

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