Dates and Set Analysis

[taml]

Hi Everyone, 

I am having a lot of trouble wrapping my head around set analysis and dates. Could someone set me on straight and narrow, please? 

I have two tables below

I am trying to get the total amount donated by each member prior to their requested statement date. I am making a total mess of this.  

Sum({$<DonationDate={"<=$(=[Statement Date])"}>}Amt)

i.e. member 2 has made a total of 3000 in donation between 01st Nov 2019 to 01st Apr 2020. The statement requested date is 31st Dec 2019, which should give 600 

Many Thanks

donation:
load * Inline [
Member ID, DonationDate, Amt
1,01/12/2019,500
1,01/01/2020,1000
1,01/02/2020,2000
2,01/11/2019,600
2,01/01/2020,600
2,01/02/2020,600
2,01/03/2020,600
2,01/04/2020,600
3,01/01/2019,2000
3,01/01/2019,2000
3,01/06/2019,2000
3,01/07/2019,2000
3,01/10/2019,2000
3,01/12/2019,5000
]
;
MemberList:
Load * Inline [
Member ID, Statement Date
1,31/03/2020
2,31/12/2019
3,30/06/2019
]
;

[Taoufiq_Zarra · Accepted solution]

@taml  why not

in dimension [Member ID]

in expression:

=sum(if(DonationDate<=[Statement Date],Amt))

output:

[Gabriel · Accepted solution]

@taml 

The set analysis below will work, however, you will have zeros when nothing is selected in Statement Date.

Your Dimension is [Member ID]

SUM({<DonationDate = {"<=$(=DATE([Statement Date]))"}>} Amt)

[taml]

Hi @Gabriel  and @Taoufiq_Zarra I took both your advice. I am clearly missing something if it worked for both of you. 

This is my output, could you steer me in the right direction please: 

[Taoufiq_Zarra]

@taml  probably you have  to check your date format , DonationDate is aligned on the left Statement Date on the right

you can try

=sum(if(Date#(DonationDate,'DD/MM/YYYY')<=Date#([Statement Date],'DD/MM/YYYY')<,Amt))

[Gabriel]

@taml 
Make a selection in Statement Date field and see there a result.

[taml]

@Gabriel @Taoufiq_Zarra  Thank you both. Not quite what I needed. Although that is more to do with me than you. 

[qlikviewwizard · Accepted solution]

donation:
load [Member ID],date(date#(DonationDate,'DD/MM/YYYY')) as DonationDate,Amt Inline [
Member ID, DonationDate, Amt
1,01/12/2019,500
1,01/01/2020,1000
1,01/02/2020,2000
2,01/11/2019,600
2,01/01/2020,600
2,01/02/2020,600
2,01/03/2020,600
2,01/04/2020,600
3,01/01/2019,2000
3,01/01/2019,2000
3,01/06/2019,2000
3,01/07/2019,2000
3,01/10/2019,2000
3,01/12/2019,5000
]
;
join (donation)
MemberList:
Load [Member ID], date(date#([Statement Date],'DD/MM/YYYY')) as [Statement Date] Inline [
Member ID, Statement Date
1,31/03/2020
2,31/12/2019
3,30/06/2019
]
;

NoConcatenate
Final:
LOAD *, if(DonationDate<=[Statement Date],Amt,0) as Donation_Amt;

LOAD * Resident donation;
drop table donation;