Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jakobjensen
Contributor II
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:

 

jakobjensen_0-1642585038926.png

I have also tried set-analysis:

 

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

 

but that gives the same error

 

1 Reply
vikasmahajan

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 

vikasmahajan_0-1643377625755.png

 

This way you can create histogram chart.

Hope this will help you

Vikas

Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.
If you want to go quickly, go alone. If you want to go far, go together.