Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Join us March 10th, 7 ways modern analytics can help you take smarter action. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
shirleyc40
Creator
Creator

Measure based off another dimension's value

Hi, 

I have a list of from and to dates and I would like to create a table to show some thing like this:

FromToNumber submitted
2/24/184/20/184658
4/21/186/15/182342
6/16/188/10/188765

Where number submitted is a count if the date is between from and to. I'm not sure how I can refer to the associated From and To date from the measure

Labels (1)
12 Replies
edwin
Specialist III
Specialist III

You aggregate it group by the from and to.  you can do this in the script or in the UI but i suspect you should do this in the UI.  you can use AGGR(count(NumberSubmitted),From, To)

MayilVahanan

HI @shirleyc40 

Try with intervalmatch concept to achieve this.

Ref: https://help.qlik.com/en-US/sense/February2021/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPref...

 

Thanks & Regards,
Mayil Vahanan R
shirleyc40
Creator
Creator

I'm not too familiar with the aggr function, but for the count, I need to count the number of applicant ids that have a recieved date between from and to

edwin
Specialist III
Specialist III

in that case, IMO @MayilVahanan 's answer is what you are looking for

duchezbr
Contributor
Contributor

Place the field name for applicant ids into the aggr function -- AGGR(count(applicant_ids),From, To)

You can test with the following data assuming your dataset looks something like this:

Table:
Load * Inline [
From|To|applicant_id
2/24/18|4/20/18|111
2/24/18|4/20/18|112
2/24/18|4/20/18|113
4/21/18|6/15/18|114
4/21/18|6/15/18|115
6/16/18|8/10/18|116
6/16/18|8/10/18|117
] (delimiter is '|');

shirleyc40
Creator
Creator

My data looks like this : I have two tables

shirleyc40_0-1614095402504.png

shirleyc40_2-1614095429333.png

 

 

shirleyc40
Creator
Creator

is it possible to use fieldvalue and rowno in an if statement to do the counting because I need to do this interval count for multiple date columns (i.e I have a recieved date, submission_Date, latest_date) 

saran7de
Master
Master

Try this,

 

tab1:
LOAD * INLINE [
    From, To
    2/24/2018, 4/20/2018
    4/21/2018, 6/15/2018
    6/16/2018, 8/10/2018
    8/11/2018, 10/5/2018
];

tab2:
LOAD * INLINE [
    received_date, app_id
    3/23/2018, 3343
    2/28/2018, 1224
    4/21/2018, 222
];

Inner Join IntervalMatch(received_date)
LOAD From, To
Resident tab1;

Drop Table tab1;

 

saran7de
Master
Master

Output:

commQV13.PNG