# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for
Did you mean:
Highlighted
Contributor II

## Dates and Set Analysis

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:
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:
Member ID, Statement Date
1,31/03/2020
2,31/12/2019
3,30/06/2019
]
;

Labels (1)
• ### Qlik Sense

3 Solutions

Accepted Solutions
Highlighted
Master

@taml  why not

in dimension [Member ID]

in expression:

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

output:

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Highlighted
Partner

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

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

Highlighted
Master II

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;

drop table donation;

7 Replies
Highlighted
Master

@taml  why not

in dimension [Member ID]

in expression:

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

output:

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Highlighted
Partner

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

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

Highlighted
Contributor II

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:

Highlighted
Master

@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))``
Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Highlighted
Partner

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

Highlighted
Contributor II

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

Highlighted
Master II

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;