Qlik Community

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 
Search instead for 
Did you mean: 
Highlighted
Contributor II
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:
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
]
;

Labels (1)
3 Solutions

Accepted Solutions
Highlighted
Master
Master

@taml  why not

in dimension [Member ID]

in expression:

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

 

output:

Capture.PNG

Regards,
Taoufiq ZARRA

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

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

View solution in original post

Highlighted
Partner
Partner

@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)

 

View solution in original post

Highlighted
Master II
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;

LOAD * Resident donation;
drop table donation;123.PNG

 

View solution in original post

7 Replies
Highlighted
Master
Master

@taml  why not

in dimension [Member ID]

in expression:

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

 

output:

Capture.PNG

Regards,
Taoufiq ZARRA

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

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

View solution in original post

Highlighted
Partner
Partner

@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)

 

View solution in original post

Highlighted
Contributor II
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: 

Screenshot_2020-10-21_11-36-39.png

 

Highlighted
Master
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
Partner

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

Highlighted
Contributor II
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
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;

LOAD * Resident donation;
drop table donation;123.PNG

 

View solution in original post