Measure based off another dimension's value

[shirleyc40]

Hi, 

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

From	To	Number submitted
2/24/18	4/20/18	4658
4/21/18	6/15/18	2342
6/16/18	8/10/18	8765

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

[edwin]

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...

[shirleyc40]

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]

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

[duchezbr]

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]

My data looks like this : I have two tables

[shirleyc40]

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) 

[Saravanan_Desingh]

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;

[Saravanan_Desingh]

Output: